如何从JSON列表提取所有项目名称并拼接展示？

你可以通过展开JSON数组后聚合拼接的方式实现需求，具体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 project

执行逻辑说明： #

1. json_extract(myblob, '$.projects') 提取完整的projects数组
2. unnest(...) 将数组展开为多行数据，每行对应一个项目对象
3. json_extract_scalar(project, '$.name') 从每行的项目对象中提取名称
4. string_agg(..., ',') 将所有项目名用逗号拼接成单个字符串

执行后会得到你期望的输出：

project_name
project1,project2

适配不同SQL环境的写法（以PostgreSQL为例） #

如果你的SQL环境支持json_array_elements函数，也可以用更简洁的写法：

WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'::json
     AS myblob
)
SELECT string_agg((project->>'name'), ',') AS project_name
FROM dataset,
     json_array_elements(myblob->'projects') AS project

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