how to deconstruct array of json with different keys in postgres?-CodePudding

I'm using postgres and I have a table like this:

id	data
1	[{a:1},{b:2}]
2	[{a:1},{b:2}]

I'm trying to retrieve a and b values with their id.

I tried this query

SELECT id,
 x.*
FROM table
cross join json_to_recordset(data::json) as x 
(   "a" varchar ,
    "b" varchar
)

but this retrieve it as

is there a way to retrieve the id and its keys in one row?

CodePudding user response：

What you need is using conditional aggregation after quoting the key names as "a" and "b"

SELECT id, MAX(a) AS a, MAX(b) AS b
  FROM t,
       json_to_recordset(data::json) as j( "a" varchar, "b" varchar ) 
 GROUP BY id      
 ORDER BY id

CodePudding user response：

You can simply access each array element:

select t.id, 
      data -> 0 ->> 'a' as a,
      data -> 1 ->> 'b' as b
from the_table t