Postgres JSONB support in Clojure via next.jdbc

jsonb_postgres_clojure.clj

(ns jsonb-postgres-clojure
  (:require
   [jsonista.core :as json]
   [next.jdbc.prepare :as prepare]
   [next.jdbc.result-set :as rs])
  (:import
   [org.postgresql.util PGobject]
   [java.sql PreparedStatement Array]))

(def mapper (json/object-mapper {:decode-key-fn keyword}))
(def ->json json/write-value-as-string)
(def <-json #(json/read-value % mapper))

(defn ->pgobject
  "Transforms Clojure data to a PGobject that contains the data as
  JSON. PGObject type defaults to `jsonb` but can be changed via
  metadata key `:pgtype`"
  [x]
  (let [pgtype (or (:pgtype (meta x)) "jsonb")]
    (doto (PGobject.)
      (.setType pgtype)
      (.setValue (->json x)))))

(defn <-pgobject
  "Transform PGobject containing `json` or `jsonb` value to Clojure
  data."
  [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (when value
        (with-meta (<-json value) {:pgtype type}))
      value)))

(defn <-arr
  "Transform PGObject containing an array to a vector, with extra support for arrays with json elements."
  [^Array a]
  (let [type (.getBaseTypeName a)
        conv (case type
               ("jsonb" "json") (fn [x] (with-meta (<-json x) {:pgtype type}))
               identity)]
    (mapv conv (.getArray a))))

;; if a SQL parameter is a Clojure hash map or vector, it'll be transformed
;; to a PGobject for JSON/JSONB:
(extend-protocol prepare/SettableParameter
  clojure.lang.IPersistentMap
  (set-parameter [m ^PreparedStatement s i]
    (.setObject s i (->pgobject m)))

  clojure.lang.IPersistentVector
  (set-parameter [v ^PreparedStatement s i]
    (.setObject s i (->pgobject v)))

  clojure.lang.Keyword
  (set-parameter [k ^PreparedStatement s i]
    (.setObject s i (name k))))

;; if a row contains a PGobject then we'll convert them to Clojure data
;; while reading (if column is either "json" or "jsonb" type):
(extend-protocol rs/ReadableColumn
  org.postgresql.util.PGobject
  (read-column-by-label [^org.postgresql.util.PGobject v _]
    (<-pgobject v))
  (read-column-by-index [^org.postgresql.util.PGobject v _2 _3]
    (<-pgobject v))

  Array
  (read-column-by-label [^Array v _]    (<-arr v))
  (read-column-by-index [^Array v _ _]  (<-arr v)))