search

babashka / babashka-sql-pods

Babashka pods for SQL databases
Eclipse Public License 1.0
85 stars 17 forks source link

Reading JSON from Postgres #25

Closed anthonygalea closed 3 years ago

anthonygalea commented 3 years ago

Problem I was trying to write a babashka script that involves reading data from postgres. Some of this data is in a json column. When my query runs I see:

Cannot read EDN: ... #object[org.postgresql.util.PGobject .... :cause "No reader function for tag object"

I've noticed that sql-pods uses the next.jdbc library so I tried following the advice here: https://cljdoc.org/d/seancorfield/next.jdbc/1.0.445/doc/getting-started/tips-tricks#working-with-json-and-jsonb but I got: 

Could not find namespace: next.jdbc.result-set.

Possible solutions

  1. Make it possible to use next.jdbc namespaces
  2. Coerce the JSON into a string when running the query and then deserialise it. (This worked fine for my usecase and was documented in https://github.com/babashka/babashka-sql-pods/commit/347e7acb1b473bf4b8b10fefc5590ba94324f2b1)
  3. Automatically convert the PGobject to a Clojure value. This would involve making some pre-determined choices which might cause surprises for the user

Note: Solution 2 worked fine in my case. Only posting this issue to identify if there are any usecases for which 2 is not appropriate.

borkdude commented 3 years ago

@anthonygalea I think 3 would be most convenient and probably what most users would expect from a postgres + clojure tool. One choice we would have to make is if the deserialized JSON value should be keywordized using keywords, for example.

anthonygalea commented 3 years ago

That sounds reasonable to me @borkdude. I think I'd opt to keywordize the keys in that case.

borkdude commented 3 years ago

There may be some good ideas here:

https://github.com/dcj/coerce/blob/develop/src/coerce/jdbc/pg.clj

borkdude commented 3 years ago

Implemented in v0.0.4.

See https://github.com/babashka/babashka-sql-pods/blob/master/CHANGELOG.md#v004.