MANU MANU - 10 months ago 51
Java Question

How to retrieve and use a json column along with non-json columns from postgres in java?

I am trying to read data from postgres db which has one column as json type. Whereas other columns are not json. Though I tried using few examples and stackoverflow code but it gives error. I tried this

ObjectMapper mapper = new ObjectMapper();
String json = (rs.getString("topic_data"));
Map<String, Object> map = new HashMap<String, Object>();
// convert JSON string to Map
map = mapper.readValue(json, new TypeReference<Map<String, String>>() {});

And getting this error:

Dao com.fasterxml.jackson.databind.JsonMappingException: Can not deserialize instance of java.lang.String out of START_ARRAY token
at [Source: {"topicHeader": "attendant windows", "words": [{"percentage": 0.1322331750852289, "word": "windows"}, {"percentage": 0.07222296435182111, "word": "get"}, {"percentage": 0.07221384639035615, "word": "computer"}, {"percentage": 0.07221264897351826, "word": "instead"}, {"percentage": 0.0722068233704654, "word": "just"}, {"percentage": 0.07202521418513595, "word": "still"}, {"percentage": 0.07181489746918468, "word": "attendant"}, {"percentage": 0.012222238496667995, "word": "don"}, {"percentage": 0.012126611197220477, "word": "really"}, {"percentage": 0.012109084263926815, "word": "ie"}, {"percentage": 0.012104725488616788, "word": "went"}, {"percentage": 0.01210188511739723, "word": "order"}, {"percentage": 0.012101477029454613, "word": "service"}, {"percentage": 0.012100753482874852, "word": "deal"}, {"percentage": 0.012100113702598862, "word": "professional"}, {"percentage": 0.012099577741692897, "word": "store"}, {"percentage": 0.012099153533334564, "word": "purchase"}, {"percentage": 0.01209459547306794, "word": "website"}, {"percentage": 0.01209311873816971, "word": "make"}, {"percentage": 0.01208934633126715, "word": "buy"}]}; line: 1, column: 36] (through reference chain: java.util.LinkedHashMap["words"])`

My json column is like :


whats the best way to solve the issue?

Answer Source

As stated by the message, [{ "word":"hello", "percentage":12.14 },{ "word":"hi", "percentage":22.14 }] is not a string.

So you should use a proper custom class:

class Foo {
    private String topicHeader;
    private List<Bar> words;
    // getter, setter, etc

class Bar {
    private String word;
    private double percentage;
    // getter, setter, etc

then Foo foo = mapper.readValue(json, Foo.class) and use foo