Postgres create table from json field

Question

I have this json field in a table A called foo (A.foo is json data).

All the foo values are just arrays of object like "[{"a": 1, "b": 2}, {"a": 3, "b": 4}]", what I want to do is go through all the foo values and generate a table like :

a | b
1 | 2
3 | 4

where a and b are the columns and the values are in rows. Any idea on how I can do that? My field is json and not jsonb

Pooya · Accepted Answer · 2021-12-16 05:35:13Z

2

According to Postgres documents you can use json_to_recordset to convert array o object to record set.

Demo

select 
  x.a,
  x.b
from 
  json_to_recordset('[{"a": 1, "b": 2}, {"a": 3, "b": 4}]')
    as x(a int, b int)

answered Dec 16, 2021 at 5:35

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres create table from json field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related