Redshift转Snowflake SQL时JSON解析多文档输入报错如何解决

你遇到的报错：

Error parsing JSON: more than one document in the input

核心诱因是cr.creatives字段为非标准JSON格式：外层多了一层包裹的双引号，内部JSON的双引号用两个双引号做了转义，直接调用PARSE_JSON无法正确识别。同时原转换语句的数组长度计算逻辑有误，ARRAY_CONSTRUCT会把整个字段值作为单个元素放入数组，计算出的长度永远为1，和Redshift原语句的逻辑不符。

SELECT 
    cr.creatives AS creatives,
    ARRAY_SIZE(PARSE_JSON(REPLACE(TRIM(cr.creatives, '"'), '""', '"'))) AS creatives_length,
    PARSE_JSON(REPLACE(TRIM(cr.creatives, '"'), '""', '"'))[0]:previewUrl::STRING AS preview_url
FROM campaign_revisions cr

• TRIM(cr.creatives, '"')：去除字段值外层多余的包裹双引号
• REPLACE(..., '""', '"')：将JSON内部用于转义的双双引号，替换为标准JSON格式要求的单个双引号
• PARSE_JSON(...)：将处理完成的标准JSON字符串解析为Snowflake支持的JSON对象
• ARRAY_SIZE(...)：直接获取JSON数组的长度，完全对应Redshift原语句中JSON_ARRAY_LENGTH的作用
• [0]:previewUrl::STRING：提取数组第一个元素的previewUrl字段值并转换为字符串类型，对应Redshift原语句中两层JSON提取函数的效果

如果表中存在部分cr.creatives为空/不符合格式的脏数据，可以用TRY_PARSE_JSON代替PARSE_JSON，避免脏数据触发查询报错，不符合格式的字段会返回NULL。

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