CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Blackdynomite
Blackdynomite

Reputation: 431

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!

Upvotes: 1

Views: 1866

Answers (2)

Simon D
Simon D

Reputation: 6269

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"
        }
     }
  ]
}')
);

Upvotes: 1

Mike Walton
Mike Walton

Reputation: 7339

Using a CTE as an example input:

WITH x AS (
  SELECT '{
  "list":[
     {
        "item":{
           "id":"aa1212dg3232",
           "name":"Joe Doe",
           "postScore":9000,
           "url":"google.com"
        }
     },
     {
        "item":{
           "id":"1111jjdjdjsdj11",
           "name":"Jane Doe",
           "postScore":12222,
           "url":"yahoo.com"
        }
     }
  ]
  }' as str
)

You just need to flatten the array after parsing the string to JSON:

SELECT y.value:item:postScore::varchar
FROM x,
LATERAL FLATTEN(input=>parse_json(str):list) y;

Upvotes: 2

Related Questions