SNOWFLAKE querying the array of elements

Question

I am using SNOW_FLAKE and trying to query the data stored in the form of array of elements under column name nested_colmn as example:

nested_colmn
[
  {
    "firstKey": "val1",
    "secondKey": 2555,
    "thirdKey": false,
    "fourthkey": "otrvalue"
  },
  {
    "firstKey": "val2",
    "secondKey": 255221,
    "thirdKey": true,
    "fourthkey": "otrvalu"
  }
]

The above Array gets returned as one complete row if I do

Select nested_colmn from table_name

Now I want to query/get the results only for the firstkey(nested_colmn.firstkey) from the Attributes column. How do I frame the query to be to retrieve the individual custom elements from an array instead of getting all. Please help me if any thoughts on this

Answer 1

Note: I will assume that you truly want the source table to have the array as a value, instead of stripping the outer array and placing each element into its own row.

First, create a test table with your sample data:

CREATE OR REPLACE TEMPORARY TABLE table_name (
  nested_colmn  VARIANT
)
AS
  SELECT PARSE_JSON($1) AS nested_colmn
    FROM VALUES
           ($$
[
  {
    "firstKey": "val1",
    "secondKey": 2555,
    "thirdKey": false,
    "fourthkey": "otrvalue"
  },
  {
    "firstKey": "val2",
    "secondKey": 255221,
    "thirdKey": true,
    "fourthkey": "otrvalu"
  }
]
$$)
;

With that, here is a sample query:

SELECT F.VALUE:"firstKey"::VARCHAR AS FIRST_KEY
  FROM table_name T
      ,LATERAL FLATTEN(nested_colmn) F
;

Answer 2

You're going to need to run a lateral flatten on the array and then parse the JSON:

WITH x as (
SELECT array_construct(parse_json('
  {
    "firstKey": "val1",
    "secondKey": 2555,
    "thirdKey": false,
    "fourthkey": "otrvalue"
  }'),parse_json('
  {
    "firstKey": "val2",
    "secondKey": 255221,
    "thirdKey": true,
    "fourthkey": "otrvalu"
  }')) as var)
SELECT p.value:firstKey::varchar
FROM x,
lateral flatten(input => x.var) p;