如何在BigQuery中解析字符串格式的字典列表？

你的输入本质是JSON数组字符串，直接按逗号拆分会破坏字典内部结构，正确做法是用BigQuery原生的JSON处理函数来解析，无需手动拆分。

解决方案代码 #

WITH logs AS (
    -- 注意：实际生产数据需保证是合法JSON（字符串值需加双引号）
    SELECT '[{"Type":"A", "ID":123, "Amount":100.5} ,{"Type":"B", "ID":456, "Amount":200.0}]' as inventory
)
SELECT
    elem AS raw_json_object,
    JSON_VALUE(elem, '$.Type') AS type,
    CAST(JSON_VALUE(elem, '$.ID') AS INT64) AS id,
    CAST(JSON_VALUE(elem, '$.Amount') AS FLOAT64) AS amount
FROM logs,
UNNEST(JSON_EXTRACT_ARRAY(inventory)) AS elem;

关键步骤说明 #

• JSON_EXTRACT_ARRAY(inventory)：将字符串格式的JSON数组转换为BigQuery可识别的数组类型，自动处理数组内部的逗号分隔逻辑
• UNNEST(...) AS elem：把数组中的每个JSON字典单独拆分为一行数据
• JSON_VALUE(elem, '$.字段名')：从每个JSON对象中提取指定字段的字符串值，配合CAST可以转换为对应的数据类型（比如整数、浮点数）

为什么之前的尝试无效 #

• ARRAY(inventory)：仅将整个字符串包装成单元素数组，未解析JSON结构
• SPLIT(SUBSTR(...), ',')：按逗号拆分时会误拆分字典内部的键值对分隔符，得到的片段不是完整JSON对象，无法被JSON_QUERY正确解析

注意事项 #

如果你的原始数据中存在非标准JSON格式（比如字符串值未加双引号），需要先清洗数据保证格式合法，否则BigQuery的JSON函数无法正常工作。

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