CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
milos.blagojevic
milos.blagojevic

Reputation: 13

Querying list in a Snowflake VARIANT file

I have a VARIANT table that has many JSON files but, for the sake of the example and to illustrate my issue, let's look at only the two rows below.

{ 
    "id" : "1", 
    "fields":
       [ 
          { 
         "id": "somekey1", 
          "value" : "value1"
            }, 
            { 
            "id": "somekey2", 
         "value" : "value2"
         }
        ]
},
{
    "id" : "2", 
    "fields":
       [ 
          { 
           "id": "somekey1", 
           "value" : "value1"
          }, 
          { 
           "id": "somekey2", 
           "value" : "value2"
          },
          { 
            "id": "somekey3", 
         "value" : "value3"
          }
       ]
}

I want to write a query that would give me this output:

ID VALUES
1 ["value1","value2","value3"]
2 ["value1","value2"]

I have tried many things and this query gave me something of a result but not remotely close to the desired output:

 SELECT
      file:id as ID,
      s.value:value::varchar as VALUES
from variant_table,
      table(flatten(FILE:fields)) s

And the result is below, plus it omits the JSON if the fields is empty:

ID VALUES
1 "value1"
1 "value2"
1 "value3"
2 "value1"
2 "value2"

What would be the best approach to solve this in Snowflake?

Upvotes: 1

Views: 533

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

After flattening, this will turn the rows into arrays for the VALUES column:

 SELECT
      file:id::int as ID,
      array_agg(s.value:value::varchar) as "VALUES"
from variant_table,
      table(flatten(FILE:fields)) s
group by ID

Upvotes: 3

Related Questions