PostgreSQL 10如何提取JSON数组中gruppe_id值并拼接为新列

实现逻辑 #

首先将JSON数组拆分为单个元素行，提取每一行的gruppe_id文本值，最后通过聚合函数用逗号拼接为单个字符串输出。

参考SQL代码 #

假设你的业务表名为your_table，存储JSON数组的列名为json_column，按行输出拼接结果的语句如下：

SELECT
  string_agg(json_elem->>'gruppe_id', ', ') AS "Gruppe_ID"
FROM your_table,
     json_array_elements(json_column) AS json_elem
-- 若表有主键，按主键分组保证每行原数据对应一行拼接结果
GROUP BY your_table.id;

函数说明 #

• json_array_elements(json_column)：PostgreSQL内置函数，用于将JSON类型的数组拆分为多行，每一行对应数组内的一个JSON对象
• ->>运算符：用于提取JSON对象指定字段的文本类型返回值
• string_agg(expression, delimiter)：聚合函数，将多行表达式结果按指定分隔符拼接为单个字符串

测试验证 #

你提供的示例数据可通过以下语句直接测试运行：

WITH test_demo(json_data) AS (
  VALUES ('[{"_destroy": false, "gruppe_id": "verwalter"}, {"_destroy": "", "gruppe_id": "budget_nur_anzeigen"}]'::JSON)
)
SELECT string_agg(elem->>'gruppe_id', ', ') AS "Gruppe_ID"
FROM test_demo, json_array_elements(json_data) elem;

运行输出结果与预期一致：

Gruppe_ID
verwalter, budget_nur_anzeigen

补充说明 #

如果部分JSON对象不存在gruppe_id字段，可添加WHERE elem ? 'gruppe_id'条件过滤无效值，避免拼接结果出现空值。 #

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