I have a variant table containing 24 JSONs that look 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"
}
My goal is to explode everything into individual rows. Currently, I have managed to achieve this for everything except the "filter" section using the following 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 like a null value if that is the case. Ideally, I would like to have table columns for:
event, user_id, app_version, context, param_page, filter_page, results, manufacturer, brand, category
I have attempted several lateral flatten queries without 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;
Unfortunately, these queries return either 8 rows or 0 rows, or they throw errors. Is there a solution to achieve the desired result? Thank you.