BigQuery：将通用单层JSON动态转换为STRUCT类型

嘿，我懂你的痛点——100多个发送方，每个的JSON键都不一样，手动写提取语句根本不现实！之前的正则方法不仅有问题，还没法转成STRUCT，我给你一套动态处理的方案，完全不用手动指定键：

完整解决方案代码 #

DECLARE select_fields STRING;

WITH input_table AS (
  SELECT 1 AS Row, 20210101 AS Date, 'Sender1' AS Sender, '{"param1": 123, "param2": 456, "param3": 78, "value1": 42, "label1": "hello", "timestamp": 1234567890}' AS Message UNION ALL
  SELECT 2 AS Row, 20210101 AS Date, 'Sender2' AS Sender, '{"value1": 4, "label1": "myLabel", "label2": "yourLabel"}' AS Message UNION ALL
  SELECT 3 AS Row, 20210102 AS Date, 'Sender1' AS Sender, '{"param1": 12, "param2": 90, "param3": 55, "value1": 11, "label1": "there", "timestamp": 1235555555}' AS Message
),
all_unique_keys AS (
  -- 提取所有不重复的JSON键
  SELECT DISTINCT key
  FROM input_table,
       UNNEST(REGEXP_EXTRACT_ALL(REPLACE(Message, '"', '"'), r'"([^"]+)"\s*:')) AS key
)
-- 拼接出每个键的提取语句
SELECT STRING_AGG(
  FORMAT('PARSE_JSON(REPLACE(Message, """, "\\""))->`%s` AS %s', key, key), 
  ', '
) INTO select_fields
FROM all_unique_keys;

-- 动态执行SQL，生成STRUCT
EXECUTE IMMEDIATE FORMAT('
  SELECT 
    Row, 
    Date, 
    Sender, 
    STRUCT(%s) AS message_struct
  FROM input_table
', select_fields);

方案说明 #

• 预处理JSON字符串：用REPLACE(Message, '"', '"')把转义引号换成标准双引号，确保BigQuery的JSON函数能正确解析内容。
• 自动提取所有键：REGEXP_EXTRACT_ALL配合正则r'"([^"]+)"\s*:'精准匹配每个JSON键，再用DISTINCT去重得到所有可能的字段，不管有多少发送方都能覆盖。
• 动态生成STRUCT：用STRING_AGG把每个键的提取逻辑拼接成字段列表，再通过EXECUTE IMMEDIATE动态执行SQL，自动把所有字段打包成STRUCT类型的message_struct列。
• 保留原始数据类型：用PARSE_JSON(...)->的方式提取值，能保留JSON里的原始类型（比如数字还是数字、字符串还是字符串），比JSON_EXTRACT_SCALAR更灵活。

为什么之前的正则不行？ #

你之前用的r':{.*?}+'匹配逻辑有问题，没法正确剔除JSON值的部分，导致键提取不准确。换成r'"([^"]+)"\s*:'可以精准定位键的位置，避免误提取内容。

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