Snowflake中Airbyte同步的Java Document对象转标准JSON方案咨询

Airbyte同步MongoDB数据至Snowflake时，Java侧的Document序列化字符串会被直接存储为文本格式，可通过Snowflake内置的正则替换+JSON解析函数完成转换，操作如下： #

转换逻辑说明 #

原始字符串的固定冗余格式为外层双引号、Document{{开头、}}结尾、键值对用=分隔，我们逐层替换冗余内容后解析为标准JSON（VARIANT类型）即可。 #

单次转换SQL示例 #

假设存储Document格式数据的列名为cost，所属表为your_table，转换代码如下：

SELECT 
  PARSE_JSON(
    -- 第四步：将处理完的标准JSON格式字符串解析为Snowflake VARIANT类型，支持直接用JSON路径提取属性
    REGEXP_REPLACE(
      -- 第三步：将所有 键=值 格式替换为 "键": "值"
      REGEXP_REPLACE(
        -- 第二步：将所有 }} 替换为 }
        REGEXP_REPLACE(
          -- 第一步：删除字符串首尾包裹的双引号
          REGEXP_REPLACE(cost, '^"|"$', ''),
          'Document\\{\\{', '{'
        ),
        '\\}\\}','}'
      ),
      '(\\w+)=(\\w*[0-9.]+\\w*|\\w+)', '"\\1": "\\2"'
    )
  ) AS cost_standard_json
FROM your_table;

封装为UDF长期复用 #

如果需要频繁执行该转换，可封装为自定义函数直接调用：

CREATE OR REPLACE FUNCTION DOCUMENT_STRING_TO_JSON(input_str STRING)
RETURNS VARIANT
LANGUAGE SQL
AS $$
PARSE_JSON(
    REGEXP_REPLACE(
        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(input_str, '^"|"$', ''),
                'Document\\{\\{', '{'
            ),
            '\\}\\}','}'
        ),
        '(\\w+)=(\\w*[0-9.]+\\w*|\\w+)', '"\\1": "\\2"'
    )
)
$$;

封装完成后调用方式：

SELECT DOCUMENT_STRING_TO_JSON(cost) AS cost_standard_json FROM your_table;

后续属性提取示例 #

转换完成后的cost_standard_json为VARIANT类型，可直接用JSON路径语法提取属性，比如提取第一条的USD金额：

SELECT TRY_CAST(cost_standard_json[0]:value::STRING AS NUMBER(18,2)) AS usd_amount
FROM (
  SELECT DOCUMENT_STRING_TO_JSON(cost) AS cost_standard_json FROM your_table
) t;

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