将BigQuery中嵌套JSON字符串转为数组并生成新表的技术求助

一、JSON字符串转数组并展开的SQL实现 #

假设你的主表为main_table，存储嵌套JSON字符串的列名为nested_json_str，以下是具体的BigQuery SQL步骤：

1. 验证并解析JSON数组
   先过滤无效JSON，再提取目标数组：

WITH parsed AS (
  SELECT
    id AS parent_id, -- 主表主键，用于关联子表
    -- 替换'$.orders'为你实际的数组路径，比如'$.items'
    JSON_EXTRACT_ARRAY(nested_json_str, '$.orders') AS nested_array
  FROM `your-project.your-dataset.main_table`
  WHERE JSON_VALID(nested_json_str) -- 跳过格式错误的JSON
)

2. 展开数组并提取字段
   用UNNEST展开数组，再提取嵌套对象的字段：

, unnested AS (
  SELECT
    parent_id,
    JSON_VALUE(item, '$.id') AS item_id,
    JSON_VALUE(item, '$.product') AS product_name,
    JSON_EXTRACT_NUMBER(item, '$.price') AS price -- 数字类型用这个函数
    -- 根据你的JSON结构添加更多字段
  FROM parsed, UNNEST(nested_array) AS item
)

3. 写入独立子表
   将展开后的数据存入单独的表，方便后续查询：

SELECT *
INTO `your-project.your-dataset.nested_items_table`
FROM unnested;

二、最优数据集结构建议 #

• 拆分主副表：主表保留Webhook推送的非嵌套核心字段（比如推送时间、请求ID等），子表存储展开后的嵌套数据，通过parent_id关联，避免单表冗余。
• 分层处理多层嵌套：如果JSON有多层嵌套（比如数组里还有数组），逐层解析展开，每层对应一个子表，比如orders表关联order_items表。
• 字段类型匹配：提取字段时用对应类型的JSON函数（JSON_EXTRACT_NUMBER、JSON_EXTRACT_BOOLEAN），避免全部存为字符串，保证数据类型正确性。

三、常见问题排查 #

• JSON路径错误：如果提取数组返回NULL，先用SELECT JSON_QUERY(nested_json_str, '$') FROM main_table LIMIT 1查看完整JSON结构，确认数组的正确路径。
• 转义字符问题：Webhook推送时可能会把JSON转义成字符串（比如带双引号转义\"），可以先用REGEXP_REPLACE(nested_json_str, r'\\', '')去除多余转义（注意先测试，避免破坏有效格式）。
• 重复数据：展开后如果出现重复行，检查原始Webhook数据是否重复推送，或者在UNNEST后加DISTINCT去重。

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