Support for Postgres JSON operators?

[xiongtx]

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.

[ukriish]

That looks like it can come in handy. Please raise a PR and I'll be happy to merge it. :)

[xiongtx]

Will consider a PR after we gain more familiarity w/ using HoneySQL w/ JSONB 😄.

The above is promising, though.

[arichiardi]

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..

[ukriish]

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.

[devn]

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: https://github.com/jkk/honeysql/issues/159

[devn]

@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.

[lsnape]

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"]

[SevereOverfl0w]

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!)