BigQuery不支持JSONPath递归运算符.. 如何实现JSON递归搜索

你需要提取JSON中嵌套在students结构内的name字段值，当前直接使用$..students.name会报Unsupported operator in JSONPath: ..错误，可通过以下两种方案实现需求：

方案1：固定层级场景（适配你的示例需求） #

如果实际业务中students的嵌套层级固定，直接写全量JSON路径即可，无需递归，你的示例可直接修改为：

SELECT JSON_EXTRACT(json_text, '$.class.students.name') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;

运行后即可得到期望输出：

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

方案2：层级不固定，通用递归搜索 #

如果students可能出现在任意层级，需要真正的递归匹配，可以用BigQuery的JavaScript UDF实现JSON递归遍历：

-- 定义递归提取的临时UDF
CREATE TEMP FUNCTION RECURSIVE_JSON_EXTRACT(json_str STRING, parent_key STRING, target_key STRING)
RETURNS STRING
LANGUAGE js AS """
  function traverse(obj, inTargetParent) {
    if (typeof obj !== 'object' || obj === null) {
      return inTargetParent ? obj : undefined;
    }
    for (const key in obj) {
      if (key === parent_key) {
        const res = traverse(obj[key], true);
        if (res !== undefined) return res;
      } else if (key === target_key && inTargetParent) {
        return obj[key];
      }
      const childRes = traverse(obj[key], inTargetParent);
      if (childRes !== undefined) return childRes;
    }
    return undefined;
  }
  const json = JSON.parse(json_str);
  return JSON.stringify(traverse(json, false));
""";

-- 调用UDF，参数依次为JSON字符串、父级匹配键students、目标提取键name
SELECT RECURSIVE_JSON_EXTRACT(json_text, 'students', 'name') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}',
-- 测试不同层级的students结构
'{"school":{"grade":{"class":{"students":{"name":"Mike"}}}}}'
]) AS json_text;

运行后输出：

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

内容的提问来源于stack exchange，提问作者py-r