如何在BigQuery SQL中提取JSON字符串的嵌套价格值？

问题原因分析 #

你之前的JSON_QUERY写法返回null，核心是JSON路径错误：

• 第一种写法直接跳过了variants、choice_groups、choices多层嵌套对象，price字段并不在variants层级下
• 第二种写法用数组索引[3]访问variants，但variants是键值对对象而非数组，数组索引语法不适用

另外，JSON中带特殊字符（冒号）的键名，访问时需要用双引号包裹。

解决方案 #

情况1：嵌套键名固定 #

如果variants、choice_groups、choices下的键名是固定的，直接使用完整JSON路径即可：

SELECT
  JSON_VALUE(change_json_string, '$.old_value.variants."652442770:1070774498".choice_groups."652442771".choices."1070774500".price') AS old_price,
  JSON_VALUE(change_json_string, '$.new_value.variants."652442770:1070774498".choice_groups."652442771".choices."1070774500".price') AS new_price
FROM
  `your-project.your-dataset.your-table`;

说明：这里用JSON_VALUE而非JSON_QUERY，因为price是标量值，JSON_VALUE直接提取无引号的字符串结果；若用JSON_QUERY，返回结果会带双引号，需额外处理。

情况2：嵌套键名动态（不固定） #

如果variants、choice_groups、choices下的键名是动态变化的，通过UNNEST展开嵌套对象的键值对提取price：

WITH parsed_json AS (
  SELECT
    JSON_EXTRACT(change_json_string, '$.old_value') AS old_val,
    JSON_EXTRACT(change_json_string, '$.new_value') AS new_val
  FROM
    `your-project.your-dataset.your-table`
),
old_price_extract AS (
  SELECT
    JSON_VALUE(choice_val, '$.price') AS old_price
  FROM
    parsed_json,
    UNNEST(JSON_QUERY_ARRAY(old_val, '$.variants.*')) AS variant_val,
    UNNEST(JSON_QUERY_ARRAY(variant_val, '$.choice_groups.*')) AS group_val,
    UNNEST(JSON_QUERY_ARRAY(group_val, '$.choices.*')) AS choice_val
),
new_price_extract AS (
  SELECT
    JSON_VALUE(choice_val, '$.price') AS new_price
  FROM
    parsed_json,
    UNNEST(JSON_QUERY_ARRAY(new_val, '$.variants.*')) AS variant_val,
    UNNEST(JSON_QUERY_ARRAY(variant_val, '$.choice_groups.*')) AS group_val,
    UNNEST(JSON_QUERY_ARRAY(group_val, '$.choices.*')) AS choice_val
)
SELECT
  old_price,
  new_price
FROM
  old_price_extract
JOIN
  new_price_extract ON TRUE;

这段SQL通过JSON_QUERY_ARRAY和UNNEST逐层展开所有动态键对应的对象，最终提取出所有层级下的price值。

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