CODEWITHSUNDEEP
snowflake-cloud-data-platform
Texnoti
Texnoti

Reputation: 31

Flatten nested JSON in snowflake

this is an example of a JSON (it can be more, or less, types and/or values. I want to end up with (order not important):

Countries, IC Countries, ES Countries, SE Countries, GB Countries, US Categories, film-chat

JSON

{
  "list": [
    {
      "element": {
        "comparison": "anyOf",
        "logical": "and",
        "type": "Countries",
        "value": {
          "list": [
            {
              "element": "IC"
            },
            {
              "element": "ES"
            },
            {
              "element": "SE"
            },
            {
              "element": "GB"
            },
            {
              "element": "US"
            }
          ]
        }
      }
    },
    {
      "element": {
        "comparison": "anyOf",
        "logical": "and",
        "type": "Categories",
        "value": {
          "list": [
            {
              "element": "film-chat"
            }
          ]
        }
      }
    }
  ]
}

What I've tried so far, probably iteration 17: Can't past more code apparently... Need more details.

Upvotes: 0

Views: 11594

Answers (4)

Texnoti
Texnoti

Reputation: 31

select
    id,
    f1.value:element:type::string type,
    f2.value:element::string element
from
    table,
    lateral flatten(input => table.json, path => 'list') f1,
    lateral flatten(input => f1.value:element:value:list) f2

Upvotes: 3

Darren Gardner
Darren Gardner

Reputation: 1222

If we assume that we have a table defined (and populated) as follows:

CREATE OR REPLACE TEMPORARY TABLE MY_TABLE (
  MY_DICT  VARIANT
)
AS
  SELECT PARSE_JSON($1) AS MY_DICT
    FROM VALUES ($$
  {
    "list": [
      {
        "element": {
          "comparison": "anyOf",
          "logical": "and",
          "type": "Countries",
          "value": {
            "list": [
              {
                "element": "IC"
              },
              {
                "element": "ES"
              },
              {
                "element": "SE"
              },
              {
                "element": "GB"
              },
              {
                "element": "US"
              }
            ]
          }
        }
      },
      {
        "element": {
          "comparison": "anyOf",
          "logical": "and",
          "type": "Categories",
          "value": {
            "list": [
              {
                "element": "film-chat"
              }
            ]
          }
        }
      }
    ]
  }
  $$)
;

Then we can use this query to return the information you specified:

SELECT LISTAGG(F1.VALUE:"element":"type"::VARCHAR || ', ' || F2.VALUE:"element"::VARCHAR, ' ')
  FROM MY_TABLE
      ,LATERAL FLATTEN(MY_TABLE.MY_DICT:"list") F1
      ,LATERAL FLATTEN(F1.VALUE:"element":"value":"list") F2
;

EDIT (based on your comment about wanting rows, not a concatenated string column):

Simply remove the LISTAGG(), as follows:

SELECT F1.VALUE:"element":"type"::VARCHAR || ', ' || F2.VALUE:"element"::VARCHAR AS TYPE_ELEMENT
  FROM MY_TABLE
      ,LATERAL FLATTEN(MY_TABLE.MY_DICT:"list") F1
      ,LATERAL FLATTEN(F1.VALUE:"element":"value":"list") F2
;

If you want a simple result set, with 2 columns (unclear from your specification), then you can just use this:

SELECT F1.VALUE:"element":"type"::VARCHAR AS TYPE
      ,F2.VALUE:"element"::VARCHAR AS ELEMENT
  FROM MY_TABLE
      ,LATERAL FLATTEN(MY_TABLE.MY_DICT:"list") F1
      ,LATERAL FLATTEN(F1.VALUE:"element":"value":"list") F2
;

Upvotes: 2

MMV
MMV

Reputation: 980

A while ago I actually wrote a piece of documentation around that:

https://community.snowflake.com/s/article/Dynamically-extract-multi-level-JSON-object-using-lateral-flatten

It allows for dynamically extracting all fields up to 4th level of nesting (and you can always add more) and then you can sort them the way you need using regular selects.

Upvotes: 4

Nat Taylor
Nat Taylor

Reputation: 1108

Are you using FLATTEN() documented at https://docs.snowflake.com/en/sql-reference/functions/flatten.html?

We need more to go on, but you'll likely end up with something like:

with tbl as (select parse_json($1) json from values ('{"list":[{"element":{"comparison":"anyOf","logical":"and","type":"Countries","value":{"list":[{"element":"IC"},{"element":"ES"},{"element":"SE"},{"element":"GB"},{"element":"US"}]}}},{"element":{"comparison":"anyOf","logical":"and","type":"Categories","value":{"list":[{"element":"film-chat"}]}}}]}'))
select *
from tbl,
  lateral flatten(json:list) list_l1 ,
  lateral flatten(list_l1.value:element) element_l1,
  lateral flatten(element_l1.value:list, OUTER => TRUE) list_l2 ,
  lateral flatten(list_l2.value:element, OUTER => TRUE) element_l2

Upvotes: 0

Related Questions