Making an array of keys from json in postgresSQL

Question

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

elements
[{"key":12,"value":"qw"},{"key":13,"value":"fa"}]
[{"key":32,"value":"24"},{"key":321,"value":"21"}]

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

elements	result
[{"key":12,"value":"qw"},{"key":13,"value":"fa"}]	{12,13}
[{"key":32,"value":"24"},{"key":321,"value":"21"}]	{32,321}

is there a way to do it? thank you

Answer 1

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

Answer 2

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;

elements	result
[{"key":12,"value":"qw"},{"key":13,"value":"fa"}]	12,13
[{"key":32,"value":"24"},{"key":321,"value":"21"}]	32,321

---

View on DB Fiddle