postgresqljsonbjsonb-api

Extract values from jsonb and merge in to a dataset row

tableABC

enter image description here

I have a PostgreS table with above structure with a JSONB column. I'm using Spring JDBC to retrieve data. Is there a way to get the row related data in the below structure, using a SQL query.

ColumnA|ColumnB|ColumnC|Output Field 1|Output Field 2|
123    |3434   |123    |100           |12            |
34     |56     |656    |1100          |22            |

I tried with the method jsonb_to_record(), but got no success.

SELECT * FROM
    tableABC,jsonb_to_record(
                columnJsonB->'Output Fields'
        ) AS x(Output Field 1 TEXT, Output Field 2 TEXT)

EDIT-------

As @Bergi mentioned jsonb_to_record works too, with proper quotes.

SELECT * FROM
        tableABC,jsonb_to_record(
                    columnJsonB->'Output Fields'
            ) AS x("Output Field 1" TEXT, "Output Field 2" TEXT)
Solution

  • All you need to do is using operator -> to extract 'Output Fields' as JSON then apply ->> to obtain field 1 & 2 as text :

    select ColumnA, ColumnB, ColumnC, 
       columnJsonB->'Output Fields'->>'Output Field 1' as "Output Field 1",
       columnJsonB->'Output Fields'->>'Output Field 2' as "Output Field 2"
from tableABC