PostgreSQL 15.4：如何解析JSON数组并聚合生成TEXT类型技能列？

场景说明 #

环境：PostgreSQL 15.4

现有表table1的创建语句：

CREATE TABLE table1 (
 id INT PRIMARY KEY,
 name TEXT,
 skills JSON
);

已插入的测试数据：

INSERT INTO table1 (id, name, skills) VALUES
 (1, 'Alice', '[
                 {"sid" : 11, "description" : "Cardio"}, 
                 {"sid" : 13, "description" : "Anesthist"}
              ]'
 ),
 (2, 'Bob', '[
               {"sid" : 10, "description" : "Gastro"}, 
               {"sid" : 9, "description" : "Oncology"}
              ]'
 ),
 (3, 'Sam', '[]'
 );

期望查询结果（skill列为TEXT类型）：

id   name     skill
---------------------
1   Alice     ["Cardio","Anesthist"]
2   Bob       ["Gastro","Oncology"]
3   Sam       []

问题分析 #

之前尝试的查询语句：

select  
id, name, d ->> 'description' as skill 
from table1, 
json_array_elements(skills) as d

该语句用隐式CROSS JOIN拆分JSON数组，导致生成重复行；同时当skills为空数组时，无匹配结果，Sam的记录会被过滤，不符合预期。

解决方案 #

方法一：LEFT JOIN LATERAL + json_agg #

SELECT
  id,
  name,
  json_agg(d->>'description')::TEXT AS skill
FROM table1
LEFT JOIN LATERAL json_array_elements(skills) AS d ON true
GROUP BY id, name;

• LEFT JOIN LATERAL保证空数组的记录不会被过滤；
• json_agg()将提取到的所有description值聚合为JSON数组，再转为TEXT类型。

方法二：JSON路径函数（PostgreSQL 12+） #

SELECT
  id,
  name,
  json_path_query_array(skills, '$[*].description')::TEXT AS skill
FROM table1;

• json_path_query_array直接从JSON数组中提取所有description字段的值，组成新的JSON数组，语法更简洁高效。

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