Postgres convert to columns from string json

Question

I am running postgresql 14.4. I have a table with 2 fields:

id   data_product(varchar)
---  ------------
1    [{"product_id":1,"product_code":"A1","distribution_ratio":40},{"product_id":2,"product_code":"A2","distribution_ratio":60}]
.........

I need to move the data to a new table with fields like:

id   product_id   product_code   distribution_ratio
---  ----------   ------------   ------------------
1        1             A1                 40
1        2             A2                 60
.........

Answer 1

You can use jsonb_to_recordset function as follows:

select id,extracted_data.*
from your_table
join lateral jsonb_to_recordset(data_product) 
as extracted_data ("product_id" int ,"product_code" text,"distribution_ratio" int) on true