BigQuery中提取JSON对象内数组所有make字段值的方法

首先修正你的测试数据插入语句（原语句存在JSON语法错误）：

INSERT INTO `project.dataset.cars`
  (id, color, objeto)
VALUES
  (1, 'Blue', JSON '{"key1": "value1", "key2": "value2", "person": {
    "id": 123,
    "name": "John Doe",
    "cars": [
      {
        "make": "Toyota",
        "model": "Camry"
      },
      {
        "make": "Honda",
        "model": "Civic"
      }
    ]
  }}');

你的原查询返回空是因为JSON_EXTRACT_ARRAY的用法错误：该函数的第二个参数是过滤数组元素的条件，而非提取字段的路径。要提取数组中所有make值，有两种常用方案：

方案1：展开数组返回多行结果（每个make单独一行） #

SELECT
  id,
  color,
  JSON_VALUE(car, '$.make') AS make
FROM
  `project.dataset.cars`,
  UNNEST(JSON_EXTRACT_ARRAY(objeto, '$.person.cars')) AS car;

方案2：返回包含所有make的数组（每行对应一个数组） #

如果需要把同一记录的所有make保留在一个数组中，可以用数组推导式：

SELECT
  id,
  color,
  ARRAY(
    SELECT JSON_VALUE(car, '$.make')
    FROM UNNEST(JSON_EXTRACT_ARRAY(objeto, '$.person.cars')) AS car
  ) AS all_makes
FROM
  `project.dataset.cars`;

简化写法（BigQuery原生JSON支持） #

因为你的objeto是JSON类型列，BigQuery允许直接用.访问属性，写法更简洁：

SELECT
  id,
  color,
  ARRAY(
    SELECT car.make
    FROM UNNEST(objeto.person.cars) AS car
  ) AS all_makes
FROM
  `project.dataset.cars`;

关键说明 #

• JSON_EXTRACT_ARRAY(objeto, '$.person.cars')：提取objeto中person.cars对应的完整数组；
• UNNEST：将数组展开为多行记录，每个数组元素对应一行；
• JSON_VALUE或直接属性访问：提取每个数组元素中的make字段值；
• ARRAY()：将展开后的多行make值重新组合为一个数组。

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