How do I parse a nested json string in Snowflake?

Question

thanks for reading and hope you can help me.

This is what my json string looks like. I'm struggling to find a way to parse it in Snowflake.

{"date":"2020-07-13T00:00:00.0000000","Reason":"{\"description\":\"Test\",\"alternates\":{},\"position\":10}","forename":"Tester","surname":"Test","title":"Mr","dateOfBirth":"2000-11-22T00:00:00.0000000"}

When I try PARSE_JSON() I get the following error

SQL Error [100069] [22P02]: Error parsing JSON: missing comma, pos 51

I'm exploring the possibility of cleansing/transforming the data before ingestion but perhaps someone out there has a better solution to deal with this issue within Snowflake.

So far I haven't been able to parse this or create a regular expression to replace the quote marks after the backwards slash.

Any help is much appreciated

Thanks!

jc

Darren Gardner · Accepted Answer · 2020-07-13 16:21:20Z

1

JCB,

I am unable to reproduce your issue. Here is what I am using:

WITH X AS (
  SELECT PARSE_JSON($1) AS MY_JSON
    FROM VALUES ($$
{
  "date": "2020-07-13T00:00:00.0000000",
  "Reason": "{\"description\":\"Test\",\"alternates\":{},\"position\":10}",
  "forename": "Tester",
  "surname": "Test",
  "title": "Mr",
  "dateOfBirth": "2000-11-22T00:00:00.0000000"
}
$$)
)
SELECT MY_JSON
  FROM X
;

Please provide the EXACT SQL that you are using, so that others here can assist you better.

answered Jul 13, 2020 at 16:21

Darren Gardner

1,2327 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

morhook Over a year ago

I had similar error as OP. My SQL looks like SELECT PARSE_JSON('{“address”:”{\”nested\”:2}”}');. With your $$ my SQL was fixed. Thanks!

jcb · Accepted Answer · 2020-07-27 13:20:57Z

0

I managed to parse the json with Darren's help. Also managed to list any new keys and attributes with a lateral join to a flatten subquery.

SELECT DISTINCT f.path, typeof(f.value) FROM REPORT_DATA, LATERAL FLATTEN(SRC, RECURSIVE=>true) f WHERE TYPEOF(f.value) != 'OBJECT';

answered Jul 27, 2020 at 13:20

jcb

133 bronze badges

Collectives™ on Stack Overflow

How do I parse a nested json string in Snowflake?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related