CODEWITHSUNDEEP
arraysjsonpostgresqlobject
HRK44
HRK44

Reputation: 2742

Postgres create table from json field

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

Upvotes: 0

Views: 412

Answers (1)

Pooya
Pooya

Reputation: 3173

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)

Upvotes: 2

Related Questions