How to update jsonb type on one table?

[ZacksTsang]

I have a column name of info on a table, it's type is jsonb, how can i update it?

[r0man]

@ZacksTsang This depends a bit on which "driver" you are using (clojure.java.jdbc, java.jdbc or NodeJS). SQLingvo is only concerned about building vectors that you can pass to those drivers.

For clojure.java.jdbc and java.jdbc you have to convert the column into a PostgresSQL JSONB type.

(defn jsonb
  "Convert `x` into a JSONB type."
  [x]
  (doto (PGobject.)
    (.setValue (json/json-str x))
    (.setType "jsonb")))

With the helper function above you can build an insert statement:

(sql/sql (sql/insert db :my-table []
           (sql/values [{:info (jsonb [1 2 3])}])))
;=> ["INSERT INTO \"my-table\" (\"info\") VALUES (?)" "[1,2,3]"]

Then pass the vector you got from the sql function to either clojure.java.jdbc or java.jdbc. Note, the "[1,2,3]" is only printed as a String, it's actually a PGObject.

I added an example of this on another project of mine that deals with those drivers. This project is experimental, but it shows how I use this in my projects:

https://github.com/r0man/datumbazo/blob/dfc03a807a83d4f5753b1872d02f44897eb81384/test/datumbazo/core_test.clj#L731

I hope that helps, r0man.

[ZacksTsang]

@r0man , thanks, this is pretty work, i will try it.

[nfedyashev]

@r0man I'm sorry for bringing this up but perhaps you can give me a hint now to do that with NodeJS&psql driver?

I've tried like 50 different variations can't get an INSERT queries for jsonb column to work

[r0man]

Hi @nfedyashev, I'm not sure how this is usually done in Node.js, but you could try this, which also works on the JVM without the PGObject:

@(sql/create-table db :my-table
   (sql/column :id :serial :primary-key? true)
   (sql/column :data :json))

@(sql/insert db :my-table []
   (sql/values [{:data '(cast "{\"a\": 1}" :json)}]))

[nfedyashev]

@r0man It worked! Thank you so much!