Parse JSON like VARCHAR Column in Snowflake

I have a VARCHAR column called TAG within a SQL database with the data looking like the example below.

Example:

{
  "list":[
     {
        "item":{
           "id":"aa1212dg3232",
           "name":"Joe Doe",
           "postScore":9000,
           "url":”google.com"
        }
     },
     {
        "item":{
           "id":"1111jjdjdjsdj11",
           "name":"Jane Doe",
           "postScore":12222,
           "url":"yahoo.com"
        }
     }
  ]
}

Since it is not a JSON column, I am having difficulty parsing through the column to get the postScore value.

I have tried to convert this from a VARCHAR to Variant datatype so that I can do something like the following to get the

SELECT d.TAGS['list']['item']['postScore']::string as PostScore
FROM data d;

Any help on how I can go about getting that postScore value from this Varchar column would be appreciated!

Answer

You can do this with parse_json and a flatten. Note that your JSON has a problem with it. Notice that your first quote character at the beginning of the word google is a weird double-quote that may cause you problems: "url":”google.com" – ” is different from ".

Anyway, assuming that your quote was an error with your copy-paste and not actually a problem with your actual JSON then you can do it like this:

Query

select
    value:item:postScore
from test_table,
     lateral flatten(input => parse_json(col1):list)

;

Sample table I used

create or replace transient table test_table as (
    select
        column1::varchar as col1
    from
    values ('{
  "list":[
     {
        "item":{
           "id":"aa1212dg3232",
           "name":"Joe Doe",
           "postScore":9000,
           "url":"google.com"
        }
     },
     {
        "item":{
           "id":"1111jjdjdjsdj11",
           "name":"Jane Doe",
           "postScore":12222,
           "url":"yahoo.com"
        }
     }
  ]
}')
);