BigQuery展平JSON字符串为独立列返回null的解决方法

你写的SQL返回全null的核心原因是用错了解析逻辑：

• 你需要解析的目标JSON是{}包裹的单层标量对象，不是[]包裹的JSON数组
• json_extract_array函数仅能识别解析数组格式的JSON，传入普通对象时函数本身就会返回null，后续基于unnest结果的字段提取自然全部返回null
• unnest的作用是把数组里的多个元素拆成多行，你的场景不存在数组结构，完全不需要这一步操作

针对你给出的单对象JSON结构，直接对原字段做路径提取即可：

SELECT
  column_json,
  JSON_EXTRACT_SCALAR(column_json, '$.city') AS city,
  JSON_EXTRACT_SCALAR(column_json, '$.country') AS country,
  JSON_EXTRACT_SCALAR(column_json, '$.email') AS email,
  JSON_EXTRACT_SCALAR(column_json, '$.province') AS province,
  JSON_EXTRACT_SCALAR(column_json, '$.state') AS state
FROM
  `your_table` AS l

你之前写的unnest(json_extract_array(...))逻辑，仅适用于column_json存储的是JSON对象数组的场景，比如字段值格式为：

[{"city": "New York", "country": "US", "email": "test1@gmail.com", "province": "", "state": "NY"}, {"city": "Los Angeles", "country": "US", "email": "test2@gmail.com", "province": "", "state": "CA"}]

这种存在多个对象嵌套在数组里的结构，才需要先拆数组再提取每个对象的字段。
另外你给出的示例JSON中city、province、state字段本身值为空字符串，提取后对应字段也会返回空字符串，不会返回null。

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