在BigQuery中从动态JSON对象数组提取指定name键值

问题场景 #

给定如下JSON数据：

{
  "actors": {
    "stooges": [
      {
        "id": 1,
        "name": "Larry"
      },
      {
        "id": 2,
        "name": "Curly"
      },
      {
        "id": 3,
        "name": "Moe"
      }
    ]
  }
}

需要提取所有stooges数组中的name字段值，得到结果["Larry", "Curly", "Moe"]。但原有指定索引的JSON_EXTRACT写法（如下）无法适配数组大小动态变化的场景：

-- Declaring a bigQuery variable 
DECLARE json_data JSON DEFAULT (SELECT PARSE_JSON('{ "actors": {"stooges": [{"id": 1,"name": "Larry"},{"id": 2,"name": "Curly"},{"id": 3,"name": "Moe"}]}}'));

-- Select statement. But this is no good for my use case since I don't want to specify element index ([0]) as the array size is dynamic
SELECT JSON_EXTRACT(json_data, '$.actors.stooges[0].name');

可行解决方案 #

方法一：数组展开+聚合

通过提取整个数组、展开元素、提取字段再聚合的方式，适配任意长度的数组：

DECLARE json_data JSON DEFAULT (SELECT PARSE_JSON('{ "actors": {"stooges": [{"id": 1,"name": "Larry"},{"id": 2,"name": "Curly"},{"id": 3,"name": "Moe"}]}}'));

SELECT ARRAY_AGG(JSON_EXTRACT_SCALAR(stooge, '$.name')) AS names
FROM UNNEST(JSON_EXTRACT_ARRAY(json_data, '$.actors.stooges')) AS stooge;

• JSON_EXTRACT_ARRAY：提取$.actors.stooges对应的完整JSON数组
• UNNEST：将数组拆分为多行记录，每个数组元素对应一行
• JSON_EXTRACT_SCALAR：从单个元素中提取name的纯字符串值（避免返回带引号的JSON格式字符串）
• ARRAY_AGG：将分散的name值重新聚合为目标数组

方法二：直接使用JSON路径通配符

利用BigQuery的JSON_QUERY_ARRAY函数，结合[*]通配符匹配数组所有元素，一步到位提取目标数组：

DECLARE json_data JSON DEFAULT (SELECT PARSE_JSON('{ "actors": {"stooges": [{"id": 1,"name": "Larry"},{"id": 2,"name": "Curly"},{"id": 3,"name": "Moe"}]}}'));

SELECT JSON_QUERY_ARRAY(json_data, '$.actors.stooges[*].name') AS names;

• 路径中的[*]会匹配数组的每一个元素，直接提取所有元素的name字段，返回结果就是["Larry", "Curly", "Moe"]，写法更简洁高效。

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