如何在雪花中横向扁平化JSON中爆炸几个列表值JSON ?

Question

我有一个包含24个json的变体表，如下所示(每行一个):

{
"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"
}

我希望所有东西都按行展开。我已经设法拥有一切，但过滤器，因为我想要的代码:

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

列表的品牌，类别和制造商可以是空的，但我需要一个空值，如果是这种情况。我想要一个包含以下列的表:

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

我已经尝试了几个横向平坦，但没有成功:

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;

无论哪种方式，我得到8行或0行，或错误。我怎么能做到呢?由于

Answer 1

在您的示例数据中，"brand "中没有数据，这意味着除非您在lateral flatten语句中添加, OUTER => TRUE，否则对其进行任何扁平化都不会返回任何数据。更像这样:

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;

这将像对基表的LEFT JOIN一样，对于任何为空的扁平数组返回NULL。