PostgreSQL中从JSON数组提取contactId并返回列表求助

问题分析 #

你尝试直接对contactList数组使用->> 'contactId'提取值，但这个操作仅适用于单个JSON对象，而contactList是JSON数组，因此无法直接获取到每个元素的contactId，导致查询返回空结果。

解决方案 #

以下提供两种可行的实现方式，根据你的contact字段类型（json或jsonb）选择对应方法：

方法1：展开数组再聚合（兼容PostgreSQL 9.3+）

先将contactList数组拆分为单个JSON对象，提取每个对象的contactId，再通过聚合函数重新组合为数组：

-- 若字段为jsonb类型，将json_array_elements替换为jsonb_array_elements
SELECT 
  json_agg(contact_item ->> 'contactId') AS contact_list
FROM users,
     json_array_elements(contact -> 'contactList') AS contact_item
GROUP BY users.id; -- 用users表的主键分组，确保对应原表每一行的结果

如果你的表没有主键，可临时用ctid分组（不推荐长期使用）：

SELECT 
  json_agg(contact_item ->> 'contactId') AS contact_list
FROM users,
     json_array_elements(contact -> 'contactList') AS contact_item
GROUP BY users.ctid;

如果需要将contactId转为数字类型（去掉引号），可以添加类型转换：

SELECT 
  json_agg((contact_item ->> 'contactId')::int) AS contact_list
FROM users,
     json_array_elements(contact -> 'contactList') AS contact_item
GROUP BY users.id;

方法2：使用JSON Path函数（PostgreSQL 12+推荐）

PostgreSQL 12及以上版本支持JSON Path语法，可直接提取数组中所有指定字段并返回新数组，写法更简洁：

-- json类型用json_path_query_array，jsonb类型用jsonb_path_query_array
SELECT 
  json_path_query_array(contact, '$.contactList[*].contactId') AS contact_list
FROM users;

同样，若要转为数字数组，可在Path表达式中添加类型转换：

SELECT 
  json_path_query_array(contact, '$.contactList[*].contactId.int()') AS contact_list
FROM users;

结果验证 #

执行上述查询后，会得到你期望的结果：

contact_list
    ------------
1  ["11111", "11112", "11113"]
2  ["21111", "21112", "21113"]

（若使用数字转换，结果会是[11111, 11112, 11113]）

内容的提问来源于stack exchange，提问作者Zelda