BigQuery字符串型JSON数组无法使用UNNEST/STRUCT解析提取字段的方法

问题根因 #

直接调用UNNEST解析报错，是因为user_names为STRING类型，不符合UNNEST要求输入为数组的规则，需先将JSON格式的字符串转换为可遍历的数组结构后再做字段提取。

可直接运行的查询模板 #

SELECT
  ud.user_id,
  JSON_VALUE(item, '$.source') AS source,
  JSON_VALUE(item, '$.name1') AS name1,
  JSON_VALUE(item, '$.name3') AS name3
FROM user_detail ud,
-- 先将JSON格式的user_names字符串解析为JSON数组，再拆分多行
UNNEST(SAFE.JSON_QUERY_ARRAY(ud.user_names, '$')) AS item

关键逻辑说明 #

• SAFE.JSON_QUERY_ARRAY(ud.user_names, '$')：将存储JSON数组的字符串，解析为ARRAY类型的数组，满足UNNEST的输入要求；前缀SAFE可避免非合法JSON的记录导致查询报错，解析失败时自动返回NULL跳过该记录，不会中断整体查询。第二个参数$代表从JSON根路径取数组，如果你的JSON字符串外层嵌套了对象（比如数组存在data键下），可将路径修改为$.data适配。
• 拆分后的每行item为单个JSON对象，通过JSON_VALUE(item, '$.字段名')即可提取对应键的字符串值；如果需要转成其他类型，可外层套CAST函数，比如CAST(JSON_VALUE(item, '$.capture_date') AS DATE)。
• JSON路径区分大小写，提取字段时需和实际JSON内的键名大小写完全匹配，否则会返回NULL。

校验调试方法 #

如果出现提取字段为NULL的情况，可先运行以下语句查看拆分后的原始JSON内容，确认键名拼写、嵌套路径是否正确：

SELECT
  ud.user_id,
  item
FROM user_detail ud,
UNNEST(SAFE.JSON_QUERY_ARRAY(ud.user_names, '$')) AS item
WHERE ud.user_id = 12829 -- 替换为你用于校验的样本记录ID

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