I want to jsonify the results of a query performed against a Postgres table containing a column of type text[], but the problem is that clojure.data.json.write-str doesn't seem to know how to handle PG arrays:
Exception Don't know how to write JSON of class org.postgresql.jdbc4.Jdbc4Array clojure.data.json/write-generic
Do I have to supply a custom handler, or is there a simpler way?
Just to wrap up, here's what works for me, putting together Jared's answer with the latest clojure.java.jdbc API changes (0.3.0-alpha5 at the time of writing) which deprecate some patterns that are commonly used (e.g. with-query-results).
First define additional handlers for every unsupported types you care about, for instance:
(extend-type org.postgresql.jdbc4.Jdbc4Array
json/JSONWriter
(-write [o out]
(json/-write (.getArray o) out)))
(extend-type java.sql.Timestamp
json/JSONWriter
(-write [date out]
(json/-write (str date) out)))
Then use with-open to perform the JSON encoding while the connection is still open (which is important at least for org.postgresql.jdbc4.Jdbc4Array):
(with-open [conn (jdbc/get-connection db)]
(json/write-str
(jdbc/query {:connection conn}
["SELECT * FROM ..."]))))
I guess this will change as the clojure.java.jdbc API evolves, but for the moment, it works.
It is better to extend IResultSetReadColumn because you are not
required to stay in the scope of with-open
Something along the following is also more concise:
(extend-protocol jdbc/IResultSetReadColumn
org.postgresql.jdbc4.Jdbc4Array
(result-set-read-column [pgobj metadata idx]
(.getArray pgobj)))
Make sure the namespace where this is placed-in is required!
cheshire is good about handling various data types and being extensible (see add-encoder)
It looks like the issue is org.postgresql.jdbc4.Jdbc4Array does not implement java.util.Collection. Try calling getArray before you serialize it.
Edit:
If it is a nested structure, then it might be best to implement a handler. clojure.data.json uses the JSONWriter protocol. You can try something like the following:
;; Note: Pseudo Code
(extend-type org.postgresql.jdbc4.Jdbc4Array
clojure.data.json/JSONWriter
(-write [o out] (clojure.data.json/-write (.getArray o) out)))
getArray before the connection is closed.