querying a multilevel array using SnowFlake

Question

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

multi_array

[
  {
    "attribute_1": "hello",
    "attribute_2": "hello1",
    "group_attrbutes": [
      {
        "grp_attr1": "tst_val",
        "grp_attr2": "test_val2"
      }
    ]
  }
]

The flatten output would be:

attribute_1 attribute_2 grp_attr1 grp_attr2
hello     hello1        tst_val  tast_val2

can any one please advise how do i flatten the group_attrbutes array so that it would get in tabular form

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2021-03-22T19:38:52

SELECT d.json
    ,f.value:attribute_1 as attribute_1
    ,f.value:attribute_2 as attribute_2
    ,g.value:grp_attr1 as grp_attr1
    ,g.value:grp_attr2 as grp_attr2
FROM (
    SELECT parse_json('[
    {
      "attribute_1": "hello",
      "attribute_2": "hello1",
      "group_attrbutes": [
        {
          "grp_attr1": "tst_val",
          "grp_attr2": "test_val2"
        }
      ]
    }
    ]') as json
) AS d,
table(flatten(input => d.json)) f,
table(flatten(input => f.value:group_attrbutes)) g
  ;

gives (with the JSON stripped out):

ATTRIBUTE_1 ATTRIBUTE_2  GRP_ATTR1  GRP_ATTR2
"hello"     "hello1"     "tst_val"  "test_val2"

querying a multilevel array using SnowFlake

1 Answers