Fork me on GitHub
#sql
<
2020-02-24
>
deleted22:02:54

anyone have advice on how to use postgres jsonb columns with next.jdbc?

valtteri10:02:40

@deleted-user I’m using following to convert from clojure maps to JSONB and back

(ns app.db
  (:require
   [app.config :as config]
   [jsonista.core :as json]
   [next.jdbc :as jdbc]
   [next.jdbc.prepare :as prepare]
   [next.jdbc.result-set :as result-set]
   [next.jdbc.sql :as sql])
  (:import
   [org.postgresql.util PGobject]))

(def ds (jdbc/get-datasource config/db-spec))

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

(defn ->pgobject [m]
  (doto (PGobject.)
    ;; eventually we should properly determine the actual type
    (.setType "jsonb")
    (.setValue (->json m))))

(defn <-pgobject [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (<-json value)
      value)))

(extend-protocol prepare/SettableParameter
  clojure.lang.IPersistentMap
  (set-parameter [m s i]
    (.setObject s i (->pgobject m))))

(extend-protocol result-set/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)))

...

valtteri10:02:14

I’m not sure if this is what you were asking but I hope it helps!

dharrigan10:02:12

that is very handy!

dharrigan14:02:25

I think was is missing is a way to query the json data, using the chaining operators.

seancorfield22:02:48

No, but if someone is willing to write up documentation on that, I'll happily add it to the PostgreSQL Tips &amp; Tricks section of the docs!

valtteri20:02:02

I had a little time at the airport and wrote up docs for working with postgres json with next.jdbc https://github.com/seancorfield/next-jdbc/pull/94

🎉 4
valtteri21:02:27

Wow! That was quick

seancorfield21:02:56

In the next release, the Tips & Tricks section will be its own page since it's grown beyond just a few paragraphs.

👍 8
teodorlu16:03:17

Thanks a lot, @U6N4HSMFW! I'm really happy to see this make its way into the official docs.

🙂 4
valtteri10:02:40

@deleted-user I’m using following to convert from clojure maps to JSONB and back

(ns app.db
  (:require
   [app.config :as config]
   [jsonista.core :as json]
   [next.jdbc :as jdbc]
   [next.jdbc.prepare :as prepare]
   [next.jdbc.result-set :as result-set]
   [next.jdbc.sql :as sql])
  (:import
   [org.postgresql.util PGobject]))

(def ds (jdbc/get-datasource config/db-spec))

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

(defn ->pgobject [m]
  (doto (PGobject.)
    ;; eventually we should properly determine the actual type
    (.setType "jsonb")
    (.setValue (->json m))))

(defn <-pgobject [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (<-json value)
      value)))

(extend-protocol prepare/SettableParameter
  clojure.lang.IPersistentMap
  (set-parameter [m s i]
    (.setObject s i (->pgobject m))))

(extend-protocol result-set/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)))

...

seancorfield21:02:56

In the next release, the Tips & Tricks section will be its own page since it's grown beyond just a few paragraphs.

👍 8