BigQuery中JSON字符串化数据UNNEST失败返回Null求助

问题根源 #

你的代码返回空数据的核心原因：

• 存储的JSON是单个嵌套对象而非数组，json_extract_array会返回空数组，UNNEST空数组自然没有数据。
• 代码中误用了request列，你实际要解析的是名为RECORD的STRING列，属于笔误。
• UNNEST仅适用于数组类型的JSON结构，单个对象无需使用该操作。

正确解法 #

根据你的JSON结构，直接使用字段提取函数解析即可，以下是两种可行方案：

方案1：直接提取指定字段

适用于只需要获取部分嵌套字段的场景：

SELECT
  id,
  -- 提取顶层嵌套对象的purpose字段
  JSON_EXTRACT_SCALAR(RECORD, '$.PaynetInstantTransfer.purpose') AS purpose,
  -- 提取嵌套的id.value
  JSON_EXTRACT_SCALAR(RECORD, '$.PaynetInstantTransfer.id.value') AS transfer_id,
  -- 提取userIp.value
  JSON_EXTRACT_SCALAR(RECORD, '$.PaynetInstantTransfer.userIp.value') AS user_ip,
  -- 提取cardType字段
  JSON_EXTRACT_SCALAR(RECORD, '$.PaynetInstantTransfer.cardType') AS card_type
FROM `table.table`
WHERE DATE(created_at) = "2023-08-31"
  AND id = 100475508
ORDER BY created_at DESC
LIMIT 2

方案2：解析为STRUCT类型

如果需要将整个嵌套对象转为结构化数据方便后续操作，可使用SAFE.PARSE_JSON：

SELECT
  id,
  -- 将整个PaynetInstantTransfer对象转为STRUCT
  SAFE.PARSE_JSON(RECORD).PaynetInstantTransfer AS transfer_struct,
  -- 直接从STRUCT中提取字段
  SAFE.PARSE_JSON(RECORD).PaynetInstantTransfer.purpose AS purpose,
  SAFE.PARSE_JSON(RECORD).PaynetInstantTransfer.id.value AS transfer_id
FROM `table.table`
WHERE DATE(created_at) = "2023-08-31"
  AND id = 100475508
ORDER BY created_at DESC
LIMIT 2

补充说明 #

• JSON_EXTRACT_SCALAR用于提取字符串类型的字段值，若字段是对象或数组，改用JSON_QUERY。
• SAFE.PARSE_JSON会将STRING类型的JSON转为JSON类型，支持直接通过.访问嵌套字段，语法更简洁。

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