如何在BigQuery中提取嵌套JSON中的指定字段

问题原因分析 #

你的查询返回null主要有两个核心原因：

1. JSON路径包含无效空格：第二个查询的路径里存在多余空格（比如. currentPrice.），这会直接导致路径解析失败。
2. 嵌套JSON需要二次解析：从第一个查询结果来看，$.payload提取出的是JSON字符串形式的数组，而非直接可解析的JSON对象/数组结构，因此需要先将该字符串转换为JSON类型再提取内部字段。

正确查询语句 #

提取productInfo.productId

SELECT
  JSON_EXTRACT_SCALAR(JSON_PARSE(JSON_EXTRACT_SCALAR(payload, "$.payload")), "$[0].productInfo.productId") AS product_id
FROM mytable

提取currentPrice.unitValue.currencyAmount

SELECT
  JSON_EXTRACT_SCALAR(JSON_PARSE(JSON_EXTRACT_SCALAR(payload, "$.payload")), "$[0].storefrontPricingList[0].currentPrice.unitValue.currencyAmount") AS currency_amount
FROM mytable

合并提取两个字段

SELECT
  JSON_EXTRACT_SCALAR(parsed_data, "$[0].productInfo.productId") AS product_id,
  JSON_EXTRACT_SCALAR(parsed_data, "$[0].storefrontPricingList[0].currentPrice.unitValue.currencyAmount") AS currency_amount
FROM (
  -- 先将提取出的JSON字符串解析为JSON结构
  SELECT JSON_PARSE(JSON_EXTRACT_SCALAR(payload, "$.payload")) AS parsed_data
  FROM mytable
)

特殊情况说明 #

如果你的payload字段中，$.payload本身就是原生JSON数组（而非字符串），那么只需去掉路径中的空格即可正常提取：

-- 提取productId
SELECT JSON_EXTRACT_SCALAR(payload, "$.payload[0].productInfo.productId") AS product_id FROM mytable

-- 提取currencyAmount
SELECT JSON_EXTRACT_SCALAR(payload, "$.payload[0].storefrontPricingList[0].currentPrice.unitValue.currencyAmount") AS currency_amount FROM mytable

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