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.

CodePudding user response：

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: