How can I read a json column from postgresql into java?

Question

This postgresql query

SELECT array_to_json(array_agg(row_to_json(c))) FROM contacts c;

brings back a single column of type json. Here is the working query in pgadmin... enter image description here

I would like to execute this same query from my Java server, which uses Jackson. What type should I read the response into? I have tried receiving the response as a PGobject variable, but after querying, I see that the variable is null.

I have also tried receiving the response into a String, but this throws the error:

java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class java.lang.String (org.postgresql.util.PGobject is in unnamed module of loader 'app'; java.lang.String is in module java.base of loader 'bootstrap')

• Andreas: Cast it to string using ::text, i.e. SELECT array_to_json(array_agg(row_to_json(c)))::text FROM contacts c then retrieve using stmt.getString(1) and parse the string normally using Jackson.

Answer 1

If the result is a json (or jsonb) you can use ResultSet.getString() to read it. Then pass that Jackson's ObjectMapper to convert it to something else.

Btw: you can simplify your aggregation to:

select jsonb_agg(to_jsonb(c)) 
from FROM contacts c;

• GNG: hmm. If I try to receive the result into a variable of type ResultSet, I get this error. java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class java.sql.ResultSet (org.postgresql.util.PGobject is in unnamed module of loader 'app'; java.sql.ResultSet is in module java.sql of loader 'platform')
• user330315: that is something completely different