如何在BigQuery中提取JSON列内数组多值并合并为单个列

核心逻辑：先把favorites列中info.music的JSON数组拆分为多行，每行对应数组中的一个音乐对象，再按字段聚合拼接字符串即可，不同主流数据库的实现语法如下：

MySQL 实现 #

8.0及以上版本（支持JSON_TABLE） #

SELECT
  GROUP_CONCAT(m.Name ORDER BY m.idx SEPARATOR ',') AS Name,
  GROUP_CONCAT(m.Singer ORDER BY m.idx SEPARATOR ',') AS Singer
FROM 你的表名 t,
JSON_TABLE(
  t.favorites,
  '$.info.music[*]' COLUMNS (
    idx FOR ORDINALITY, -- 保留原数组顺序
    Name VARCHAR(255) PATH '$.Name',
    Singer VARCHAR(255) PATH '$.Singer'
  )
) AS m
-- 若需要按原表每行数据单独返回结果，加上下面的分组条件，t.id替换为你的表主键字段
GROUP BY t.id;

5.7版本（无JSON_TABLE） #

可以通过递归CTE遍历数组下标实现，示例如下：

WITH RECURSIVE seq AS (
  SELECT 0 AS idx UNION ALL SELECT idx + 1 FROM seq WHERE idx < 100 -- 上限调整为超过你的数组最大长度即可
)
SELECT
  GROUP_CONCAT(JSON_UNQUOTE(JSON_EXTRACT(t.favorites, CONCAT('$.info.music[', seq.idx, '].Name'))) ORDER BY seq.idx SEPARATOR ',') AS Name,
  GROUP_CONCAT(JSON_UNQUOTE(JSON_EXTRACT(t.favorites, CONCAT('$.info.music[', seq.idx, '].Singer'))) ORDER BY seq.idx SEPARATOR ',') AS Singer
FROM 你的表名 t
JOIN seq ON seq.idx < JSON_LENGTH(t.favorites, '$.info.music')
GROUP BY t.id;

PostgreSQL 实现 #

SELECT
  string_agg((m ->> 'Name')::VARCHAR, ',' ORDER BY ordinality) AS Name,
  string_agg((m ->> 'Singer')::VARCHAR, ',' ORDER BY ordinality) AS Singer
FROM 你的表名 t,
-- 如果字段类型是json而非jsonb，改为json_array_elements即可
jsonb_array_elements(t.favorites -> 'info' -> 'music') WITH ORDINALITY AS arr(m, ordinality)
-- 若需要按原表每行数据单独返回结果，加上下面的分组条件，t.id替换为你的表主键字段
GROUP BY t.id;

SQL Server 实现 #

SELECT
  STRING_AGG(m.Name, ',') WITHIN GROUP (ORDER BY m.[key]) AS Name,
  STRING_AGG(m.Singer, ',') WITHIN GROUP (ORDER BY m.[key]) AS Singer
FROM 你的表名 t
CROSS APPLY OPENJSON(t.favorites, '$.info.music')
WITH (
  Name VARCHAR(255) '$.Name',
  Singer VARCHAR(255) '$.Singer'
) AS m
-- 若需要按原表每行数据单独返回结果，加上下面的分组条件，t.id替换为你的表主键字段
GROUP BY t.id;

注意事项 #

• 拼接时如果需要保留原数组的顺序，一定要保留示例中的排序逻辑（ORDER BY idx/ordinality/[key]）
• 如果需要对拼接值去重，可以在聚合函数内加DISTINCT关键字，比如GROUP_CONCAT(DISTINCT m.Name ...)
• 如果数组为空，聚合结果会返回NULL，可以用COALESCE函数转成空字符串

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