BigQuery：将JSON列表字符串列UNNEST拆分为多行

针对你遇到的问题，核心是先把字符串格式的JSON数组解析为SQL可处理的数组类型，再通过UNNEST（或对应引擎的等价函数）展开为单独行。以下是主流SQL引擎的实现示例：

BigQuery 实现 #

SELECT
  ROW_NUMBER() OVER(ORDER BY t.TICKET_ID, update_item) AS ROW,
  t.TICKET_ID,
  t.Subject,
  update_item AS UPDATE
FROM
  your_table t
CROSS JOIN UNNEST(JSON_EXTRACT_ARRAY(t.UPDATES)) AS update_item

PostgreSQL 实现 #

SELECT
  ROW_NUMBER() OVER(ORDER BY t.TICKET_ID, update_item) AS ROW,
  t.TICKET_ID,
  t.Subject,
  update_item::text AS UPDATE
FROM
  your_table t
CROSS JOIN LATERAL json_array_elements(t.UPDATES::json) AS update_item

关键注意点 #

1. 解析JSON数组：必须先把字符串类型的UPDATES列转换为数组/JSON类型，比如BigQuery的JSON_EXTRACT_ARRAY、PostgreSQL的::json+json_array_elements，这是展开的前提。如果跳过这一步直接UNNEST，自然无法拆分。
2. 关联展开：用CROSS JOIN（或PostgreSQL的LATERAL JOIN）将数组元素与原表的每行数据关联，确保每个数组元素都能携带原表的TICKET_ID、Subject字段。
3. 行号生成：通过ROW_NUMBER()函数生成你期望的连续行号，匹配输出格式。

排查失败原因 #

如果之前尝试没成功，大概率是以下情况：

• JSON字符串格式有误：比如存在未正确转义的字符，导致解析数组失败，可以单独执行JSON_EXTRACT_ARRAY(t.UPDATES)验证输出是否为有效数组。
• 引擎函数使用错误：不同SQL引擎处理JSON的函数差异很大，比如MySQL用JSON_TABLE，需要对应调整语法。

内容的提问来源于stack exchange，提问作者Kazi Abdul Muktadir