如何将BigQuery中JSON表的数据插入嵌套结构表？

你的问题出在JSON_EXTRACT_ARRAY返回的是**ARRAY类型（每个元素是JSON格式的字符串），但目标表的addresses字段要求是ARRAY<STRUCT<street STRING, city STRING, country STRING>>**类型，两者无法直接匹配。需要把数组中的每个JSON字符串解析成对应的STRUCT，再重新组成数组。

方案1：使用JSON_EXTRACT_SCALAR遍历数组元素 #

INSERT INTO users (id, name, email, addresses)
SELECT 
  -- id是数值类型，需将JSON提取的字符串转为INT64
  CAST(JSON_EXTRACT_SCALAR(json_column, '$.id') AS INT64) AS id,
  JSON_EXTRACT_SCALAR(json_column, '$.name') AS name,
  JSON_EXTRACT_SCALAR(json_column, '$.email') AS email,
  -- 遍历JSON数组，将每个元素转换为STRUCT
  ARRAY(
    SELECT AS STRUCT
      JSON_EXTRACT_SCALAR(addr, '$.street') AS street,
      JSON_EXTRACT_SCALAR(addr, '$.city') AS city,
      JSON_EXTRACT_SCALAR(addr, '$.country') AS country
    FROM UNNEST(JSON_EXTRACT_ARRAY(json_column, '$.addresses')) AS addr
  ) AS addresses
FROM json_data

方案2：使用PARSE_JSON简化解析（更直观） #

PARSE_JSON可直接将整个JSON字符串转为JSON对象，之后能直接访问属性，写法更简洁：

INSERT INTO users (id, name, email, addresses)
SELECT 
  CAST(PARSE_JSON(json_column).id AS INT64) AS id,
  PARSE_JSON(json_column).name AS name,
  PARSE_JSON(json_column).email AS email,
  ARRAY(
    SELECT AS STRUCT
      addr.street,
      addr.city,
      addr.country
    FROM UNNEST(PARSE_JSON(json_column).addresses) AS addr
  ) AS addresses
FROM json_data

关键说明 #

1. id字段处理：JSON_EXTRACT_SCALAR返回字符串类型，必须通过CAST转为INT64才能匹配目标表字段类型。
2. 数组处理逻辑：先用UNNEST拆分JSON数组为单个元素，将每个元素解析为STRUCT后，再用ARRAY()重新组合成STRUCT数组。

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