在BigQuery中将JSON字符串数组转换为结构体数组并展开

可以通过BigQuery的JSON函数结合UNNEST来实现需求，具体SQL如下：

SELECT
  t.id,
  JSON_VALUE(item, '$.date') AS date,
  SAFE_CAST(JSON_VALUE(item, '$.value1') AS FLOAT64) AS value1,
  JSON_VALUE(item, '$.reason') AS reason,
  IFNULL(JSON_VALUE(item, '$.comment'), '') AS comment
FROM
  `your_project.your_dataset.your_table` t
LEFT JOIN UNNEST(IFNULL(JSON_EXTRACT_ARRAY(t.description, '$'), [])) AS item
ON TRUE

关键步骤说明： #

1. 解析JSON数组：JSON_EXTRACT_ARRAY(t.description, '$')将description字段的字符串格式JSON数组转为BigQuery的JSON数组类型；用IFNULL处理无效JSON或空字符串，将其转为空数组[]避免报错。
2. 展开数组：LEFT JOIN UNNEST(...) ON TRUE会把数组中的每个JSON对象展开为单独的行，同时保留原表中所有行（包括数组为空的记录，比如whdjgf723）。
3. 提取字段值：
   • JSON_VALUE(item, '$.date')从每个JSON对象中提取date字段，不存在则返回NULL；
   • SAFE_CAST(...) AS FLOAT64将value1的字符串值转为浮点型，SAFE_CAST确保转换失败时返回NULL而非中断查询；
   • IFNULL(JSON_VALUE(item, '$.comment'), '')处理可选的comment字段，不存在时返回空字符串，与示例格式一致。

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