Snowflake Retrieve value from Semi Structured Data

Question

I'm trying to retrieve the health value from Snowflake semi structured data in a variant column called extra from table X.

An example of the code can be seen below:

[
  {
    "party":
 "[{\"class\":\"Farmer\",\"gender\":\"Female\",\"ethnicity\":\"NativeAmerican\",\"health\":2},
{\"class\":\"Adventurer\",\"gender\":\"Male\",\"ethnicity\":\"White\",\"health\":3},
{\"class\":\"Farmer\",\"gender\":\"Male\",\"ethnicity\":\"White\",\"health\":0},
{\"class\":\"Banker\",\"gender\":\"Female\",\"ethnicity\":\"White\",\"health\":0}
  }
] 

I have tried reading the Snowflake documentation from https://community.snowflake.com/s/article/querying-semi-structured-data

I have also tried the following queries to flatten the query:

SELECT result.value:health AS PartyHealth 
FROM X 
WHERE value = 'Trail'
AND name = 'Completed' 
AND PartyHealth > 0, 
TABLE(FLATTEN(X, 'party')) result

AND

SELECT [0]['party'][0]['health'] AS Health
FROM X 
WHERE value = 'Trail'
AND name = 'Completed' 
AND PH > 0;

I am trying to retrieve the health value from table X from column extra which contains the the variant party, which has 4 repeating values [0-3]. Im not sure how to do this is someone able to tell me how to query semi structured data in Snowflake, considering the documentation doesn't make much sense?

Answer 1

First, the JSON value you posted seems wrong formatted (might be a copy paste issue).

Here's an example that works:

• first your JSON formatted:

  [{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]

• create a table to test:

  CREATE OR REPLACE TABLE myvariant (v variant);

• insert the JSON value into this table:

  INSERT INTO myvariant SELECT PARSE_JSON('[{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]');

• now, to select a value you start from column name, in my case v, and as your JSON is an array inside, I specify first value [0], and from there expand, so something like this:

  SELECT v[0]:party[0].health FROM myvariant;

Above gives me:

For the other rows you can simply do:

SELECT v[0]:party[1].health FROM myvariant;
SELECT v[0]:party[2].health FROM myvariant;
SELECT v[0]:party[3].health FROM myvariant;

Answer 2

Another option might be to make the data more like a table ... I find it easier to work with than the JSON :-)

Code at bottom - just copy/paste and it runs in Snowflake returning screenshot below.

Key Doco is Lateral Flatten

 SELECT  d4.path, d4.value 
 from  
 lateral flatten(input=>PARSE_JSON('[{ "party": [ {"class":"Farmer","gender":"Female","ethnicity":"NativeAmerican","health":2}, {"class":"Adventurer","gender":"Male","ethnicity":"White","health":3}, {"class":"Farmer","gender":"Male","ethnicity":"White","health":0}, {"class":"Banker","gender":"Female","ethnicity":"White","health":0} ] }]') ) as d  ,  
 lateral flatten(input=> value) as d2 ,  
 lateral flatten(input=> d2.value) as d3 ,  
 lateral flatten(input=> d3.value) as d4