CODEWITHSUNDEEP
javajsonpostgresql
GNG
GNG

Reputation: 1541

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

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')

Upvotes: 1

Views: 2631

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 1

Related Questions