Support json and jsonb Postgres types in Clojure.

pg_test.types.clj

;; For supporting more PG types, see https://github.com/remodoy/clj-postgresql
(ns pg-test.types
  (:require [cheshire.core :as json]
            [clojure.java.jdbc :as jdbc])
  (:import  [org.postgresql.util PGobject]
            [java.sql PreparedStatement]))


;; Writing
(defn- to-pg-json [data json-type]
  (doto (PGobject.)
    (.setType (name json-type))
    (.setValue (json/generate-string data))))


(defn parameter-dispatch-fn [_ type-name]
  (keyword type-name))


(defmulti map->parameter parameter-dispatch-fn)


(defmethod map->parameter :json
  [m _]
  (to-pg-json m :json))


(defmethod map->parameter :jsonb
  [m _]
  (to-pg-json m :jsonb))


(extend-protocol jdbc/ISQLParameter
  ;; Convert Clojure maps to SQL parameter values
  clojure.lang.IPersistentMap
  (set-parameter [m ^PreparedStatement s ^long i]
    (let [meta (.getParameterMetaData s)]
      (if-let [type-name (keyword (.getParameterTypeName meta i))]
        (.setObject s i (map->parameter m type-name))
        (.setObject s i m)))))


;; Reading
(defmulti read-pgobject
  "Convert returned PGobject to Clojure value."
  #(keyword (when (some? %) (.getType ^PGobject %))))


(defmethod read-pgobject :json
  [^PGobject x]
  (when-let [val (.getValue x)]
    (json/parse-string val true)))


(defmethod read-pgobject :jsonb
  [^PGobject x]
  (when-let [val (.getValue x)]
    (json/parse-string val true)))


(defmethod read-pgobject :default
  [^PGobject x]
  (.getValue x))


;; Extend clojure.java.jdbc's protocol for interpreting ResultSet column values.
(extend-protocol jdbc/IResultSetReadColumn
  ;; PGobjects have their own multimethod
  PGobject
  (result-set-read-column [val _ _]
    (read-pgobject val)))


;; How to use:
;; (require 'pg-test.types)
;; (require '[clojure.java.jdbc :as jdbc])
;;
;; (jdbc/query conn ["select data::json from testing"])

Thanks, this helped a lot!