handle multiple double quotes & apostrophe in json key value in sql

loving_guy 0
Viewed 20
metadata 
-----------
{'a': 'jay', 'b': '100', 'c': ""ANAND'S STORE"", 'd': '200'}

Having difficulty parsing this in sql

Need to get like this:

a       b          c             d
----------------------------------------
jay | 100   |  ANAND'S STORE |  200

I have tried json_extract_path_text(metadata, 'c') as store_name but throwing error Error parsing JSON: more than one document in the input

I am open to solution in mysql or postgresql also.

snowflake-cloud-data-platform mysql sql
1 Answers

For Snowflake:

The problem is double double quotes, is how you escape quote inside CSV, but this is JSON, and thus double double quotes do not mean anything, so if we replace the double doubles, with a single double, for this data we are all good.

select column1 as a
    ,replace(a, '""','"') as b
    ,parse_json(b) as c
    ,c:a::text as c_a
from values 
('{\'a\': \'jay\', \'b\': \'100\', \'c\': ""ANAND\'S STORE"", \'d\': \'200\'}');

gives:

A B C C_A
{'a': 'jay', 'b': '100', 'c': ""ANAND'S STORE"", 'd': '200'} {'a': 'jay', 'b': '100', 'c': "ANAND'S STORE", 'd': '200'} { "a": "jay", "b": "100", "c": "ANAND'S STORE", "d": "200" } jay
Simeon Pilgrim0
Related