如何从JSON数组中选取多个值并拼接结果

我有如下示例JSON数据，想要提取所有项目名称并拼接成逗号分隔的字符串，而非通过索引仅获取单个名称（因为数组大小不固定）。当前执行的SQL只能得到project1，需要将project1和project2合并输出。

原SQL代码 #

WITH dataset AS 
(
   SELECT '{\"name\": \"Bob Smith\",
             \"org\": \"engineering\",
             \"projects\": [{\"name\":\"project1\", \"completed\":false},{\"name\":\"project2\", \"completed\":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

修改后的SQL代码 #

WITH dataset AS 
(
   SELECT '{\"name\": \"Bob Smith\",
             \"org\": \"engineering\",
             \"projects\": [{\"name\":\"project1\", \"completed\":false},{\"name\":\"project2\", \"completed\":true}]}'
     AS myblob
)
SELECT string_agg(json_extract_scalar(project, '$.name'), ',') AS project_name
FROM dataset,
     unnest(json_extract(myblob, '$.projects')) AS t(project)

代码说明 #

• 使用json_extract(myblob, '$.projects')提取完整的projects数组
• 通过unnest将数组拆分为单行记录，每个项目对象单独占一行
• 最后用string_agg函数将所有项目名称按逗号分隔拼接成字符串

期望输出 #

project_name
project1,project2

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