BigQuery中对象名称动态变化时如何访问嵌套JSON对象？

BigQuery原生支持该访问需求，无需提前获知中间层的固定键名，根据使用场景可以选择以下几种实现方式：

• 通配符路径直接穿透取值
  针对存储为原生JSON类型的字段，可在JSON路径中使用*作为单层任意键的通配符，直接跳过名称未知的中间层定位目标元素。
  举个例子，假设表中JSON字段的中间层键名完全动态不固定，需要提取所有节点下的inner_value字段：

  {
    "任意动态键1": {"inner_value": "值1"},
    "随机命名键2": {"inner_value": "值2"}
  }
  

  直接使用通配符路径查询即可，返回结果为所有匹配到的目标值组成的数组：

  SELECT JSON_QUERY_ARRAY(json_col, '$.*.inner_value') AS all_target_values
  FROM your_table
  

  如果存在多层名称未知的中间键，对应增加通配符即可，比如两层动态键就写路径$.*.*.inner_value。
• 动态键拆解后灵活取值
  如果需要同时获取动态键本身的名称、或者要针对键名做过滤/聚合逻辑，可以先用JSON_KEYS提取指定层级的所有键名，通过UNNEST展开后再拼接路径取值：

  SELECT
    dynamic_key_name,
    JSON_VALUE(json_col, CONCAT('$.', dynamic_key_name, '.inner_value')) AS target_value
  FROM your_table,
  UNNEST(JSON_KEYS(json_col)) AS dynamic_key_name
  

  这个方案灵活性更高，适合需要统计不同动态键下值分布、按键名做筛选的场景。
• 字符串格式JSON兼容处理
  如果JSON内容是存在STRING类型字段中、没有转为原生JSON类型，先调用PARSE_JSON将字符串转为原生JSON结构，再套用上面两种方法即可：

  WITH parsed_json AS (
    SELECT PARSE_JSON(json_string_field) AS json_col FROM your_table
  )
  SELECT JSON_QUERY_ARRAY(json_col, '$.*.inner_value') AS all_target_values
  FROM parsed_json
  

注意：上述通配符方案仅匹配固定深度的层级，如果未知键的嵌套深度也不固定，可以使用JSON_SEARCH函数先检索目标键的完整路径，再基于返回的路径提取值，该方案性能弱于固定深度的通配符查询，优先确认嵌套深度后选择通配符方案。

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