CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jeffrey Jacobs
Jeffrey Jacobs

Reputation: 332

Snowflake PARSE_JSON Failure on Apparently Valid JSON

I have the JSON below, which is (allegedly) from a JSON payload.

I have verified it against several different JSON parsers. But Snowflake TRY_PARSE_JSON returns NULL and PARSE_JSON returns "Error parsing JSON: missing comma, line 2, pos 28".

STRICT_JSON_OUTPUT is false.

{

"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"}}]" }

Upvotes: 0

Views: 1503

Answers (3)

akshindesnowflake
akshindesnowflake

Reputation: 601

After i remove double quotes before and after Value from Key:Value Json string , it got parsed. Js look at this json string

{
"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"}}] }

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26130

so the JSON is valid, in so mush is it is really

{ "Target_Payload__c": "long string" }

the question then becomes is Snowflake trying to parse the Stringfied embedded JSON, as JSON and getting upset, OR seeing the escape token \ but getting upset that it is not itself escaped, because it is parsing it.

If you were testing this like

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

then yes, the escape tokens will need to be escaped, and the above will error, thus it should be:

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

If your SQL is in a Stored Procedure, then as it parses SQL it also will want the escapes doubled.

But that doesn't sound the case if you are selecting the SQL from a table, and just parsing it. But you could test via

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

this would show if it's double slash based.

Upvotes: 0

Jeffrey Jacobs
Jeffrey Jacobs

Reputation: 332

It passed two online parsers. The problem is quotes surrounding the array, i.e. it should be "Target_Payload__c": [ not "Target_Payload__c": " and the trailing quote also needs to be removed.

Upvotes: 1

Related Questions