如何在Snowflake SQL中解析含重复键的类事件负载为JSON

核心问题分析 #

JSON规范不允许对象中存在重复键，直接生成带重复utc键的字符串必然无法被parse_json()正常解析。你的原始数据里，utc键总是紧跟在日期字段（如a_date、another_date）之后，核心思路是把utc和对应的日期字段关联，调整键名或结构，生成合法的JSON。

方案一：重命名UTC键（推荐，便于直接访问） #

通过拆分原字段为单行键值对，追踪每个utc对应的前置日期字段，将utc重命名为{日期字段名}_utc，比如a_date_utc、another_date_utc。

示例SQL（以Snowflake为例）

WITH split_rows AS (
    -- 拆分原字段为每行，过滤无效行
    SELECT 
        object,
        ROW_NUMBER() OVER (PARTITION BY object ORDER BY seq) AS row_num,
        TRIM(value) AS kv_pair
    FROM my_table,
    LATERAL SPLIT_TO_TABLE(object, '\n')
    WHERE TRIM(value) NOT IN ('---', '')
),
kv_parsed AS (
    -- 拆分键值对，同时获取前一个日期类型的字段名
    SELECT 
        object,
        row_num,
        TRIM(SPLIT_PART(kv_pair, ':', 1)) AS key_name,
        TRIM(SPLIT_PART(kv_pair, ':', 2)) AS key_value,
        LAG(CASE WHEN key_name LIKE '%date' THEN key_name END) OVER (PARTITION BY object ORDER BY row_num) AS prev_date_key
    FROM split_rows
),
adjusted_kv AS (
    -- 重命名UTC键为对应日期字段+_utc
    SELECT 
        object,
        row_num,
        CASE 
            WHEN key_name = 'utc' AND prev_date_key IS NOT NULL THEN CONCAT(prev_date_key, '_utc')
            ELSE key_name
        END AS adjusted_key,
        -- 转义值中的双引号，避免JSON解析错误
        REPLACE(key_value, '"', '\\"') AS safe_value
    FROM kv_parsed
)
-- 拼接成合法JSON并解析
SELECT 
    object,
    PARSE_JSON('{' || LISTAGG('"' || adjusted_key || '": "' || safe_value || '"', ', ') WITHIN GROUP (ORDER BY row_num) || '}') AS parsed_json
FROM adjusted_kv
GROUP BY object;

生成的JSON示例：

{
  "field_one": "1",
  "field_two": "20",
  "field_three": "4",
  "id": "1234",
  "another_id": "5678",
  "some_text": "Hey you",
  "a_date": "2022-11-29",
  "a_date_utc": "2022-11-29 15:29:28.159296000 Z",
  "another_date": "2022-11-30",
  "another_date_utc": "2022-11-30 13:34:59.000000000 Z"
}

方案二：嵌套日期与UTC结构（语义化） #

把日期字段和对应的utc合并为嵌套对象，更清晰地表达两者的关联关系。

示例SQL（以Snowflake为例）

WITH split_rows AS (
    SELECT 
        object,
        ROW_NUMBER() OVER (PARTITION BY object ORDER BY seq) AS row_num,
        TRIM(value) AS kv_pair
    FROM my_table,
    LATERAL SPLIT_TO_TABLE(object, '\n')
    WHERE TRIM(value) NOT IN ('---', '')
),
kv_parsed AS (
    SELECT 
        object,
        row_num,
        TRIM(SPLIT_PART(kv_pair, ':', 1)) AS key_name,
        TRIM(SPLIT_PART(kv_pair, ':', 2)) AS key_value,
        LAG(CASE WHEN key_name LIKE '%date' THEN key_name END) OVER (PARTITION BY object ORDER BY row_num) AS prev_date_key
    FROM split_rows
),
nested_kv AS (
    SELECT 
        object,
        row_num,
        -- 为日期和UTC分组
        CASE 
            WHEN key_name LIKE '%date' THEN key_name
            WHEN key_name = 'utc' AND prev_date_key IS NOT NULL THEN prev_date_key
            ELSE key_name
        END AS group_key,
        CASE 
            WHEN key_name LIKE '%date' THEN 'value'
            WHEN key_name = 'utc' AND prev_date_key IS NOT NULL THEN 'utc'
            ELSE NULL
        END AS nested_key,
        REPLACE(key_value, '"', '\\"') AS safe_value
    FROM kv_parsed
)
SELECT 
    object,
    PARSE_JSON('{' || LISTAGG(
        CASE 
            WHEN nested_key IS NOT NULL THEN 
                '"' || group_key || '": {"' || nested_key || '": "' || safe_value || '"}'
            ELSE 
                '"' || group_key || '": "' || safe_value || '"'
        END, ', ') WITHIN GROUP (ORDER BY row_num) || '}') AS parsed_json
FROM nested_kv
GROUP BY object;

生成的JSON示例：

{
  "field_one": "1",
  "field_two": "20",
  "field_three": "4",
  "id": "1234",
  "another_id": "5678",
  "some_text": "Hey you",
  "a_date": {"value": "2022-11-29", "utc": "2022-11-29 15:29:28.159296000 Z"},
  "another_date": {"value": "2022-11-30", "utc": "2022-11-30 13:34:59.000000000 Z"}
}

适配不同数据库的注意点 #

• 如果使用PostgreSQL，将SPLIT_TO_TABLE替换为regexp_split_to_table，LISTAGG替换为STRING_AGG
• 如果使用BigQuery，用UNNEST(SPLIT(object, '\n'))拆分行，STRING_AGG做拼接

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