How to explode several list values JSON within JSON with lateral flatten in Snowflake?

Question

I have a variant table with 24 JSONs like the following (one per row):

{
  "context": "marketplace",
  "metadata": {
    "app_version": "1.0.4 (166)",
  },
  "params": {
    "filter": {
      "brands": [],
      "categories": [
        "f28c7c9f-09ae-4218-821a-bec344998289"
      ],
      "manufacturers": [],
      "page": 1,
      "product_name": "",
    },
    "page": "product_list",
    "results": 3
  },
  "user_id": "6443a2db-4526-4fc5-8084-290fc78e5336"
}

I want everything exploded in rows. I have managed to have everything but filter as I want with the code:

SELECT data:event::string,
       data:user_id::string,
       data:metadata.app_version::string,
       data:context::string,
       data:params.page::string,
       data:params.filter.page::string,
       data:params.results::string
FROM ods.stg_tanimis_events

The lists brands, categories and manufacturers can be empty but I would need a null value if that is the case. I would like to have a table with the following columns:

event, user_id, app_version, context, param_page, filter_page, results, manufacturer, brand, category

I have tried with several lateral flatten with no success:

select * FROM table
, lateral flatten (data:params:filter:categories) j2;

select * FROM table
, lateral flatten (data:params:filter.brands) j1

select * FROM table
, lateral flatten (data:params:filter:brands) j1
, lateral flatten (data:params:filter:categories) j2;

select user_id, filter, flat.*
from table
, lateral flatten(parse_json(filter)) flat;


WITH j as (
SELECT *
FROM table
, lateral flatten(data:params:filter))

SELECT *
from j,
lateral flatten (j.value) j2;

Either way, I get 8 rows or 0 rows, or errors. How could I make it? Thanks

Mike Walton Mike Walton · Accepted Answer · 2021-01-20T17:49:23

In your example data, there is no data in "brands", which means any flatten against that will return nothing unless you add , OUTER => TRUE in your lateral flatten statement. Something more like this:

SELECT table.data:event::string,
   table.data:user_id::string,
   table.data:metadata.app_version::string,
   table.data:context::string,
   table.data:params.page::string,
   table.data:params.filter.page::string,
   table.data:params.results::string,
   j1.value::string as brands,
   j2.value::string as categories
FROM table
, lateral flatten (data:params:filter:brands, OUTER => TRUE) j1
, lateral flatten (data:params:filter:categories, OUTER => TRUE) j2;

This will act like a LEFT JOIN to the base table and return NULL for any flattened arrays that are empty.

How to explode several list values JSON within JSON with lateral flatten in Snowflake?

1 Answers