Extract values from jsonb with Data type and merge in to a dataset row

29 Views Asked by At

enter image description here

I have a PostgreS table with above structure with a JSONB column. I'm using Spring jdbcTemplate to retrieve data(jdbcTemplate.queryForList()).

I need to read the output in the below format.

[
 {
   "id":1,
   "Order Value" : 18.095,
   "Order Id": "ABC123"
 },
 {
   "id":2,
   "Test1" : "Value1"
 }
]

If I knew the fields inside the JSON beforehand, I could've use something like below.

SELECT * FROM
        tableABC,jsonb_to_record(
                    jsonColumn->'Output Fields'
            ) AS x("Order Value" DECIMAL, "Output ID" TEXT)

But we don't know the possible fields, that comes inside the "Output Fields" JSON.

0

There are 0 best solutions below