I have column "elements" in table which is having a json(array json) row values which looks like this

I want to make an column of arrays for every row which consist of keys extracted from that row's json values ,my desired column "result" may look like this

is there a way to do it? thank you

CodePudding user response：

Schema (PostgreSQL v13)

CREATE TABLE test (
  elements json
);
INSERT INTO test VALUES ('[{"key":12,"value":"qw"},{"key":13,"value":"fa"}]');
INSERT INTO test VALUES ('[{"key":32,"value":"24"},{"key":321,"value":"21"}]');

Query #1

select elements::text, array_agg(cast(value->>'key' as integer)) as result 
from test, json_array_elements(elements)
group by 1
ORDER BY 1;

View on DB Fiddle

CodePudding user response：

select elements::text,
       array_agg(value->>'key') 
from your_table, json_array_elements(elements)
group by 1;