解析JSON时出错:输入中有多个文档(Redshift到Snowflake SQL)

krugob8w  于 2022-11-19  发布在  其他
关注(0)|答案(2)|浏览(136)

我正在尝试将一个查询从Redshift转换为Snowflake SQL。
Redshift查询如下所示:

SELECT 
                cr.creatives as creatives
                , JSON_ARRAY_LENGTH(cr.creatives) as creatives_length
                , JSON_EXTRACT_PATH_TEXT(JSON_EXTRACT_ARRAY_ELEMENT_TEXT (cr.creatives,0),'previewUrl') as preview_url
FROM campaign_revisions cr

雪花查询如下所示:

SELECT 
                cr.creatives as creatives
                , ARRAY_SIZE(TO_ARRAY(ARRAY_CONSTRUCT(cr.creatives))) as creatives_length
                , PARSE_JSON(PARSE_JSON(cr.creatives)[0]):previewUrl as preview_url
FROM campaign_revisions cr

看起来JSON_EXTRACT_PATH_TEXT没有正确转换,因为Snowflake查询产生错误:
解析JSON时出错:输入中有多个文档
cr.creatives的格式如下:

"[{""previewUrl"":""https://someurl.com/preview1.png"",""device"":""desktop"",""splitId"":null,""splitType"":null},{""previewUrl"":""https://someurl.com/preview2.png"",""device"":""mobile"",""splitId"":null,""splitType"":null}]"
gcmastyq

gcmastyq1#

在我看来,您似乎没有在Snowflake中使用有效的JSON数据。
请检查用于复制到命令的文件格式。
如果您在文本编辑器中打开“JSON”文本,请注意,由于您使用的引号,该信息没有被解析或格式化为JSON。

正确的JSON在左边||右侧原始数据

如果您不想重新加载数据,请查看是否可以创建一个Javascript用户定义函数来删除字符串中的引号,然后可以使用Snowflake来处理变量列。
下面的代码是工作POJO,可用于为您删除双引号。

var textOriginal = '[{""previewUrl"":""https://someurl.com/preview1.png"",""device"":""desktop"",""splitId"":null,""splitType"":null},{""previewUrl"":""https://someurl.com/preview2.png"",""device"":""mobile"",""splitId"":null,""splitType"":null}]';

function parseText(input){
    var a = input.replaceAll('""','\"');
    a = JSON.parse(a);
    return  a;
}

x = parseText(textOriginal);
console.log(x);

2izufjch

2izufjch2#

对于在Snowflake外部阶段的CSV文件中看到JSON字段中出现双引号问题的其他人(与发布的原始问题略有不同):
这个问题可能是您需要使用FIELD_OPTIONALLY_ENCLOSED_BY设定。特别是在设定档案格式时,FIELD_OPTIONALLY_ENCLOSED_BY = '"'。(docs)
创建此类文件格式的示例:

create or replace file format mydb.myschema.my_tsv_file_format
type = CSV
field_delimiter = '\t'
FIELD_OPTIONALLY_ENCLOSED_BY = '"';

以及使用此文件格式从阶段进行查询的示例:

select
  $1 field_one
  $2 field_two
  -- ...and so on
from '@my_s3_stage/path/to/file/my_tab_separated_file.csv'  (file_format => 'my_tsv_file_format')

相关问题