BigQuery中单列表存储的JSON如何提取指定数组与字段值

第一步排错操作 #

既然已经可以成功提取sessions[0].variables数组，说明Column1的JSON解析链路完全正常。先把第一个会话的完整对象拉出来做结构核对：

SELECT JSON_QUERY(Column1, '$.sessions[0]') AS first_session_full_json
FROM Table

这一步会直接返回第一个会话节点的完整JSON，用来核对线上实际的字段名、大小写、嵌套层级——很多线上数据会和示例结构出现偏差，比如字段名带空格、大小写不一致、嵌套位置调整，都是提取返回空的常见诱因。

分场景正确提取写法 #

1. 提取标量字段（如conversation URL字段） #

BigQuery的JSON函数对返回值类型有严格区分，是最容易出现错配的环节：

• JSON_VALUE仅能提取路径匹配到的标量值（字符串、数字、布尔），如果路径指向对象/数组，会直接返回NULL；用它提取标量值时，会自动去掉值外层的引号，返回纯文本值
• 如果常规写法提取conversation返回空，可以加上格式兼容参数，适配URL这类带特殊字符的值：

  JSON_VALUE(Column1, '$.sessions[0].conversation' FORMAT JSON) AS conversation_url
  

• 注意BigQuery的JSONPath是严格区分大小写的，实际JSON里conversation如果是大写开头，路径写错大小写就会返回空，核对时要逐字匹配。

2. 提取数组字段（如sessionMessages、events数组） #

之前用JSON_QUERY_ARRAY提取返回空，通常是两个原因：

• 路径不匹配：和标量字段的规则一致，严格区分大小写，先核对数组字段的实际拼写和层级
• 返回结构不符合函数要求：JSON_QUERY_ARRAY要求路径必须指向一个纯数组结构，如果数组内部元素不合法、或者路径匹配到的是单个JSON对象，函数会直接返回NULL。
  对应的适配写法：
• 如果只需要拿到数组的原始JSON串，不需要转成结构化ARRAY类型，直接用JSON_QUERY即可，不需要硬套JSON_QUERY_ARRAY：

  -- 提取sessionMessages的完整JSON串
  JSON_QUERY(Column1, '$.sessions[0].sessionMessages') AS session_messages_json,
  -- 提取events的完整JSON串
  JSON_QUERY(Column1, '$.sessions[0].events') AS events_json
  

• 如果需要把数组展开为多行、或者转成结构化ARRAY类型，先确认上面的JSON_QUERY可以正常返回数组内容，再嵌套JSON_QUERY_ARRAY：

  SELECT 
    JSON_VALUE(Column1, '$.sessions[0].conversation' FORMAT JSON) AS conversation_url,
    msg
  FROM Table,
  UNNEST(JSON_QUERY_ARRAY(Column1, '$.sessions[0].sessionMessages')) AS msg
  

高频避坑要点 #

• 不要混用JSON函数的适用场景：JSON_VALUE对标量值、JSON_QUERY对对象/数组、JSON_QUERY_ARRAY对可展开的纯数组，用错函数类型是返回空值的最高发原因
• 不要默认线上数据和示例结构完全一致：每次遇到提取空值，先拉对应层级的完整JSON核对，避免凭记忆写路径
• BigQuery的JSONPath不做自动大小写兼容，字段名大小写错一个字符就会返回NULL

内容的提问来源于stack exchange，提问作者Cristian Avendaño