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

Is the data type of this column really json or does only the data looks like a json, but the data type of this column is varchar or anything else? — Jonas Metzler
– Jonas Metzler, Commented Apr 20, 2022 at 5:38

YuTing · Accepted Answer · 2022-04-20 07:29:29Z

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

edited Apr 20, 2022 at 7:29

answered Apr 20, 2022 at 7:02

YuTing

6,6692 gold badges8 silver badges19 bronze badges

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

1 Comment

Jonas Metzler Over a year ago

Well done, thank you. But I think you also should add ORDER BY 1 after the GROUP BY 1

jian · Accepted Answer · 2022-04-20 06:22:56Z

1

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

answered Apr 20, 2022 at 6:22

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Making an array of keys from json in postgresSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related