BigQuery解析含数组字段JSON时JSON_EXTRACT_SCALAR返回null问题

我之前也踩过手动拆分JSON数组的坑，太容易出问题了！你现在遇到的JSON_EXTRACT_SCALAR返回null的情况，根源就在手动处理JSON字符串的方式上——这种拆分+正则拼接的操作很容易破坏JSON的合法结构，导致后续解析失败。咱们换用BigQuery原生的JSON函数来处理，靠谱得多。

问题根源分析 #

你原来的方法用SPLIT(json, '},{')拆分数组元素，再用正则拼接成单个JSON对象，但这种方式有几个致命缺陷：

• 原始JSON数组被双引号包裹（比如你的示例"[{...}, {...}]"），拆分后的首尾元素会残留"、[或]，导致拼接后的JSON格式无效
• 万一数组元素内部包含转义后的},{（标准JSON里字符串会转义，但手动拆分还是会误判），会直接把单个元素拆成多个，彻底破坏结构
  无效的JSON对象自然会让JSON_EXTRACT_SCALAR返回null

正确的解决方案 #

用BigQuery内置的JSON_EXTRACT_ARRAY函数直接解析数组，再通过UNNEST展开元素，最后提取字段。步骤简单且可靠：

1. 先清理原始JSON字符串：去掉首尾的双引号
2. 用JSON_EXTRACT_ARRAY将字符串转为JSON数组
3. 展开数组，逐个提取字段并转换类型

完整SQL示例 #

SELECT
  ARRAY(
    SELECT
      STRUCT(
        CAST(json_item ->> '$.index' AS INT64) AS index,
        TIMESTAMP_MILLIS(CAST(json_item ->> '$.startTime' AS INT64)) AS startTime
      )
    FROM UNNEST(JSON_EXTRACT_ARRAY(REGEXP_REPLACE(json, r'^"|"$', ''))) AS json_item
  ) AS split_items
FROM (
  SELECT json FROM `dataset.table`
)

代码细节解释 #

• REGEXP_REPLACE(json, r'^"|"$', '')：去掉原始JSON字符串首尾的双引号，得到纯数组格式的[{...}, {...}]
• JSON_EXTRACT_ARRAY(...)：将清理后的字符串解析为BigQuery的JSON数组类型，完全遵循JSON规范
• UNNEST(...) AS json_item：展开数组，每个元素作为单独的合法JSON对象
• json_item ->> '$.index'：这是JSON_EXTRACT_SCALAR(json_item, '$.index')的简写，直接提取字符串类型的字段值，再转换为INT64
• 最后用ARRAY(...)将提取后的STRUCT重新组合为数组，和你原来期望的输出结构完全一致

这种方法完全依赖BigQuery的原生JSON解析引擎，能处理各种合法的JSON格式，再也不会因为手动拆分导致结构破坏，自然就解决了JSON_EXTRACT_SCALAR返回null的问题。

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