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.
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.