在PostgreSQL中获取所有JSON字段键的数组

Question

在PostgreSQL中获取所有JSON字段键的数组

10

我有一个名为user的表，表中有一个字段称为friends，该字段是json类型，并且其值如下所示。

{"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}}}

这条记录有一个 blockList 对象，其中包含被屏蔽用户的对象。我需要的是返回一个像这样的所有 block list 键的数组。

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

请问如何编写一个plpgsql函数，以将所有对象键返回到数组中？我是postgresql的初学者，请帮帮我。

- Amjad Omari

3个回答

2

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

我已经在这里创建了一个SQL fiddle，供演示使用。

注意: user是一个保留字，所以我将表格称为users。

- Imran

2

我来晚了，但我想提出一种方法，借鉴自Erwin 这里:

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

- Anton Bessonov

当你需要从表中获取其他列时，这似乎是一个更好的答案。 - undefined

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marth · Accepted Answer

使用json_object_keys函数来取出JSON对象的最外层键名，并将其作为一个集合返回（因此需要先选择包含blockList键的对象，可以使用friends->'blockList'），然后使用array_agg函数将它们聚合成一个数组：

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

注意:
如果你使用的是jsonb类型（而不是json），则需要使用jsonb_object_keys函数。