BigQuery无JSONPath递归运算符..时如何实现JSON对象递归搜索

架构老林

2026-10-2

BigQuery 无JSONPath递归运算符时的递归提取方案

BigQuery 原生JSONPath语法目前不支持递归下降运算符..，可以通过以下两种方案实现无需指定上层键名提取目标字段的需求。

方案1：使用自定义JS UDF实现递归搜索（推荐，兼容性最强）

通过临时JS UDF实现递归遍历JSON所有键的逻辑，匹配到目标键后直接返回对应值，适配你的场景的示例代码如下：

-- 定义递归提取指定键的JS UDF
CREATE TEMP FUNCTION json_recursive_extract(json_str STRING, target_key STRING)
RETURNS STRING
LANGUAGE js AS """
  function recursiveSearch(obj, key) {
    if (typeof obj !== 'object' || obj === null) return null;
    if (key in obj) return JSON.stringify(obj[key]);
    for (const k in obj) {
      const res = recursiveSearch(obj[k], key);
      if (res !== null) return res;
    }
    return null;
  }
  const json = JSON.parse(json_str);
  return recursiveSearch(json, target_key);
""";

-- 调用示例
SELECT json_recursive_extract(json_text, 'name') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;

运行后输出完全符合期望：

+-----------------+
| first_student   |
+-----------------+
| "Jane"          |
+-----------------+

方案说明

支持任意嵌套层级的JSON结构，不需要提前知晓上层键名
当前逻辑遇到多个同名目标键时会返回第一个匹配到的值，如需返回全部匹配结果，调整UDF返回数组类型即可
普通业务场景下性能足够，超大规模数据处理时可配合递归CTE+原生JSON拆解逻辑进一步优化性能

方案2：正则提取（仅适合简单场景）

如果处理的JSON结构简单无特殊转义，也可以用正则快速匹配，优势是无需创建UDF：

SELECT REGEXP_EXTRACT(json_text, r'"name"\s*:\s*("[^"]*")') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;