get array of all json field keys in postgresql

Question

I have a table called user, and inside the table there is a field called friends, this field is a json type has a value as in the following example

{"blockList": {"199": {"date": 1453197190, "status": 1}, "215": {"date": 1459325611, "status": 1}, "219": {"date": 1454244074, "status": 1}, "225": {"date": 1453981312, "status": 1}, "229": {"date": 1459327685, "status": 1}}, "followers": {"211": {"date": 1452503369}, "219": {"date": 1452764627}, "334": {"date": 1456396375}}, "following": {"215": {"date": 1459325619}, "219": {"date": 1453622322}, "226": {"date": 1454244887}, "229": {"date": 1459327691}}, "friendList": {"213": {"date": 1453622410, "type": 2, "status": 1}, "214": {"date": 1452763643, "status": 1}, "215": {"date": 1455606872, "type": 2, "status": 2}, "218": {"date": 1453280047, "status": 1}, "219": {"date": 1453291227, "status": 2}, "221": {"date": 1453622410, "type": 2, "status": 1}, "224": {"date": 1453380152, "type": 2, "status": 1}, "225": {"date": 1453709357, "type": 2, "status": 2}, "226": {"date": 1454244088, "type": 2, "status": 1}, "229": {"date": 1454326745, "type": 2, "status": 2}}}

this record has a blockList object that is containning objects for blocked users. what I need, is to return an array of all block list keys like this

["199", "215", "219", "225", "229"]

any help how can I write a plpgsql function to do that (return all object keys in an array)? I'm a beginner in psotgresql, and need a help please.

Marth · Accepted Answer · 2016-04-12 14:41:34Z

16

Use json_object_keys to a set containing the outermost keys of a json object (so you'll need to select the object for the blockList key, which you can do with friends->'blockList'), and use array_agg to aggregate them into an array:

SELECT ARRAY_AGG(f) 
FROM (
  SELECT json_object_keys(friends->'blockList') f
  FROM users
) u;
┌───────────────────────┐
│       array_agg       │
├───────────────────────┤
│ {199,215,219,225,229} │
└───────────────────────┘
(1 row)

Note:
If you're using the jsonb type (and not the json one) you'll need to use the jsonb_object_keys function.

edited Apr 12, 2016 at 14:41

answered Apr 12, 2016 at 14:34

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

3 Comments

Amjad Omari Over a year ago

ERROR: function json_object_keys(jsonb) does not exist LINE 4: SELECT json_object_keys(friends->'blockList') f ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Marth Over a year ago

If you're using jsonb then you'll need to use the jsonb_object_keys function.

Imran Over a year ago

Whoops I forgot array_agg, nice answer.

Anton Bessonov · Accepted Answer · 2021-10-26 23:20:07Z

5

I'm late to the party, but I would like to propose following way, borrowed from Erwin here:

SELECT ARRAY(SELECT json_object_keys(friends->'blockList')) FROM users;

answered Oct 26, 2021 at 23:20

Anton Bessonov

9,8833 gold badges41 silver badges39 bronze badges

1 Comment

Antonio Serrano Over a year ago

This seems to be a better answer when you have to get other columns from the table.

Imran · Accepted Answer · 2016-04-12 14:44:49Z

2

SELECT array_agg(ks) FROM ( SELECT json_object_keys(friends->'blockList') AS ks FROM users ) x

I have created a SQL fiddle here to demonstrate.

Note: user is a reserved word, so I have called the table users.

edited Apr 12, 2016 at 14:44

answered Apr 12, 2016 at 14:29

Imran

13.6k8 gold badges69 silver badges82 bronze badges

Collectives™ on Stack Overflow

get array of all json field keys in postgresql

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related