Snowflake PARSE_JSON在明显有效的JSON上失败

Question

我有下面的JSON，它(据说)来自JSON有效负载。

我已经在几个不同的JSON解析器上验证了它。但是雪花TRY_PARSE_JSON返回NULL，PARSE_JSON返回“解析JSON时出错:缺少逗号，第2行，位置28”。

STRICT_JSON_OUTPUT为false。

{

"Target_Payload__c"："{"BillingState":"OH"，"LastModifiedById":"0053R000000KbvFQAS"，"Id":"0013R000002kqE4QAI"，"ShippingState":"CA"，"CreatedDate":"2021-06-09T22:39:11.000+0000"，"ShippingPostalCode":"94509"，“BillingStreet”：“西四街231号”，“BillingCountry”：“美国”，“类型”：“分析师”，“ShippingStreet”：“西四街231号”，"SystemModstamp":"2021-06-09T22:39:11.000+0000"，"IsDeleted":false，"ParentId":"0013R000002kJfPQAU"，"BillingPostalCode":"45202"，"ShippingCity":"Antioch"，“ShippingCountry”：“美国”，"Phone":"4082393817"，"CreatedById":"0053R000000KbvFQAS"，"OwnerId":"0053R000000KbvFQAS"，“行业”：“农业”，“姓名”：“Nibeditatest”，"NumberOfEmployees":23，“网站”：“ww.nibi.com”，"IsActive":false，“BillingCity”：“辛辛那提”，"Description":"test123"，"LastModifiedDate":"2021-06-09T22:39:11.000+0000"，"attributes":{"url":"/services/data/v51.0/sobjects/Account/0013R000002kqE4QAI"，“IsActive”：“BillingCity”}“}”

Answer 1

它通过了两个在线解析器。问题是数组周围的引号，即它应该是"Target_Payload__c"：[而不是"Target_Payload__c"：“，并且尾随的引号也需要删除。

Answer 2

所以JSON是有效的，实际上它是真的

{ "Target_Payload__c": "long string" }

然后问题就变成了Snowflake试图将Stringfied嵌入式JSON解析为JSON而感到不安，或者看到转义标记\但因为它正在解析它而对它本身没有进行转义而感到不安。

如果你像这样测试

SELECT '{ "Target_Payload__c": "long string with embedded \"double quotes\"" }' as json_str,
    PARSE_JSON(json_str) as json;

然后是的，转义标记将需要被转义，并且上面的代码将会出错，因此它应该是：

SELECT '{ "Target_Payload__c": "long string with embedded \\"double quotes\\"" }' as json_str,
    PARSE_JSON(json_str) as json;

如果您的SQL在存储过程中，那么当它解析SQL时，它也会希望转义加倍。

但是，如果您是从一个表中选择SQL并只是对其进行解析，情况就不是这样了。但是你可以通过

SELECT PARSE_JSON( REPLACE(json_str, '\\', '\\\\') ) as json
FROM table;

这将显示它是否是基于双斜杠的。

Answer 3

从Key:Value Json字符串中删除Value前后的双引号后，它被解析了。Js看看这个json字符串

{
"Target_Payload__c": [{"BillingState":"OH","LastModifiedById":"0053R000000KbvFQAS","Id":"0013R000002kqE4QAI","ShippingState":"CA","CreatedDate":"2021-06-09T22:39:11.000+0000","ShippingPostalCode":"94509","BillingStreet":"231 West 4th Street","BillingCountry":"United States","Type":"Analyst","ShippingStreet":"231 West 4th Street","SystemModstamp":"2021-06-09T22:39:11.000+0000","IsDeleted":false,"ParentId":"0013R000002kJfPQAU","BillingPostalCode":"45202","ShippingCity":"Antioch","ShippingCountry":"United States","Phone":"4082393817","CreatedById":"0053R000000KbvFQAS","OwnerId":"0053R000000KbvFQAS","Industry":"Agriculture","Name":"Nibeditatest","NumberOfEmployees":23,"Website":"ww.nibi.com","IsActive":false,"BillingCity":"Cincinnati","Description":"test123","LastModifiedDate":"2021-06-09T22:39:11.000+0000","attributes":{"url":"/services/data/v51.0/sobjects/Account/0013R000002kqE4QAI","type":"Account"}}] }