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

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.

  • 1

    You’ll need dynamic SQL in a plpgsql routine. You also won’t be able to dynamically return it as is, but only as a record you still have to map, or to a table you’ll read from in another, separate step.

    – 




Leave a Comment