如何在BigQuery中动态规范化嵌套JSON并拆分至STRING类型表

方案一：SQL直接提取字段（对应json.normalize逻辑） #

假设嵌套JSON数据已加载到BigQuery临时表raw_data中，我们可以用JSON_EXTRACT_STRING直接提取目标字段，分别生成两张全STRING类型的表：

生成用户主表（含header、id、姓名、出生日期） #

CREATE OR REPLACE TABLE project.dataset.user_main_table AS
SELECT
  JSON_EXTRACT_STRING(raw_json, '$.header') AS header,
  JSON_EXTRACT_STRING(raw_json, '$.id') AS id,
  JSON_EXTRACT_STRING(raw_json, '$.firstname') AS firstname,
  JSON_EXTRACT_STRING(raw_json, '$.lastname') AS lastname,
  JSON_EXTRACT_STRING(raw_json, '$.dob') AS dob
FROM project.dataset.raw_data;

生成地址表 #

如果address是单个嵌套对象：

CREATE OR REPLACE TABLE project.dataset.user_address_table AS
SELECT
  JSON_EXTRACT_STRING(raw_json, '$.id') AS user_id, -- 保留关联主键
  JSON_EXTRACT_STRING(raw_json, '$.address.street') AS street,
  JSON_EXTRACT_STRING(raw_json, '$.address.city') AS city,
  JSON_EXTRACT_STRING(raw_json, '$.address.zipcode') AS zipcode
FROM project.dataset.raw_data;

如果address是数组类型，需要用UNNEST展开：

CREATE OR REPLACE TABLE project.dataset.user_address_table AS
SELECT
  JSON_EXTRACT_STRING(raw_json, '$.id') AS user_id,
  JSON_EXTRACT_STRING(addr_item, '$.street') AS street,
  JSON_EXTRACT_STRING(addr_item, '$.city') AS city,
  JSON_EXTRACT_STRING(addr_item, '$.zipcode') AS zipcode
FROM project.dataset.raw_data,
UNNEST(JSON_QUERY_ARRAY(raw_json, '$.address')) AS addr_item;

JSON_EXTRACT_STRING会直接将提取的JSON值转为STRING类型，完全匹配你要求的松散类型规范

方案二：先加载为Record类型再拆分 #

1. 先创建包含Record类型的原始表，将JSON数据加载进去（加载时指定schema，比如把address设为RECORD类型）
2. 再通过SQL转换为全STRING类型的拆分表：

生成用户主表 #

CREATE OR REPLACE TABLE project.dataset.user_main_table AS
SELECT
  CAST(header AS STRING) AS header,
  CAST(id AS STRING) AS id,
  CAST(firstname AS STRING) AS firstname,
  CAST(lastname AS STRING) AS lastname,
  CAST(dob AS STRING) AS dob
FROM project.dataset.raw_record_table;

生成地址表 #

如果address是单个嵌套对象：

CREATE OR REPLACE TABLE project.dataset.user_address_table AS
SELECT
  CAST(id AS STRING) AS user_id,
  CAST(address.street AS STRING) AS street,
  CAST(address.city AS STRING) AS city,
  CAST(address.zipcode AS STRING) AS zipcode
FROM project.dataset.raw_record_table;

如果address是重复数组类型：

CREATE OR REPLACE TABLE project.dataset.user_address_table AS
SELECT
  CAST(id AS STRING) AS user_id,
  CAST(addr_item.street AS STRING) AS street,
  CAST(addr_item.city AS STRING) AS city,
  CAST(addr_item.zipcode AS STRING) AS zipcode
FROM project.dataset.raw_record_table,
UNNEST(address) AS addr_item;

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