Support for Postgres JSON operators?
Opened this issue ยท 8 comments
Is there any interest in adding Postgres JSON functions and operators?
E.g. I've been using the following in my own code:
(require '[clojure.string :as str])
(require '[honeysql.format :as h-format])
(defmethod h-format/fn-handler "->" [_ val index]
(let [[val index] (map h-format/to-sql [val index])]
(if (number? index)
(format "%s->%s" val index)
(format "%s->'%s'" val index))))
(defmethod h-format/fn-handler "->" [_ val index]
(let [[val index] (map h-format/to-sql [val index])]
(if (number? index)
(format "%s->>%s" val index)
(format "%s->>'%s'" val index))))
(defmethod h-format/fn-handler "#>" [_ val path]
(format "%s#>'{%s}'"
(h-format/to-sql val)
(->> path
(map h-format/to-sql)
(str/join ","))))
This allows us to write e.g.:
(-> (h/select [(sql/call :#> :value [:a :b]) :value])
(h/from :my_table))
to produce the query SELECT value#>'{a,b}' AS value FROM my_table
.
That looks like it can come in handy. Please raise a PR and I'll be happy to merge it. :)
Will consider a PR after we gain more familiarity w/ using HoneySQL w/ JSONB ๐.
The above is promising, though.
Is there any development on this one? I am interested as well and wanted to roll my own in case @xiongtx is busy at the moment..
I've been using quite a lot of JSONB in my daily work, sadly not in clojure so haven't had time to work on this. That said, I think this is very useful and would like to add this feature to honeysql-postgres soon.
I can consider working on this in a week or two. I'm away for a week on vacation.
I'm interested in this also. I ran into this HN comment while googling, and it looked nice: https://news.ycombinator.com/item?id=17498349
CC: @emidin since I saw him post on the referenced issue: seancorfield/honeysql#159
@xiongtx FWIW, one issue with your original snippet is that it doesn't handle nested cases. For instance, a->b->>c
, would be nice to represent as (sql/call :->> (sql/call :-> :a :b) :c)
or something. (sql/json (:a :-> :b :->> :c))
would be nicer still.
I think this more or less gets you what you want:
(defn json-get
[val ks as-text?]
(reduce (fn [acc [k next]]
(str acc
(if (and as-text? (nil? next)) " ->> " " -> ")
(if-not (number? k)
(format "'%s'" (fmt/to-sql k))
k)))
(fmt/to-sql val)
(partition-all 2 1 ks)))
(defmethod fmt/fn-handler "->" [_ val ks]
(json-get val ks false))
(defmethod fmt/fn-handler "->>" [_ val ks]
(json-get val ks true))
(honey/format
(-> (hsql/select (honey/call :->> :s.data [:foo :bar]))
(hsql/from :baz)))
;; ["SELECT s.data -> 'foo' ->> 'bar' FROM foo"]
For #>
instead of using a string/join to build an array literal, an array should be passed instead. This will allow for escaping parameters as required (no sql injection then!)