sql - 展平雪花中变体 json 列中的多个名称数组
问题描述
我有一个网络爬虫将数据转储到雪花数据库中的变体列中。这是在为页面中找到的各种表创建 json 数组时创建页面数据。
这是我使用足球类比找到的 json 类型的示例:
{
"dom_url": "https://www.soccertables.com/european_tables",
"event_id": "01b2722a-d8e6-4f67-95d0-8dd7ba088a4a",
"event_utc_time": "2020-05-11 09:01:14.821",
"ip_address": "125.238.134.96",
"table_1": [
{
"position": "1",
"team_name": "Liverpool",
"games_played": "29",
"games_won": "26",
"games_drawn": "2",
"games_lost": "1",
"goals_for": "75",
"goals_against": "35"
"points": "80"
},
{
"position": "2",
"team_name": "Man. City",
"games_played": "29",
"games_won": "20",
"games_drawn": "5",
"games_lost": "4",
"goals_for": "60",
"goals_against": "45"
"points": "65"
},
{
"position": "...",
"team_name": "...",
"games_played": "...",
"games_won": "...",
"games_drawn": "...",
"games_lost": "...",
"goals_for": "...",
"goals_against": "..."
"points": "..."
}
],
"table_2": [
{
"position": "1",
"team_name": "Bayern Munich",
"games_played": "29",
"games_won": "26",
"games_drawn": "2",
"games_lost": "1",
"goals_for": "75",
"goals_against": "35"
"points": "80"
},
{
"position": "2",
"team_name": "Bayer Leverkussen",
"games_played": "29",
"games_won": "20",
"games_drawn": "5",
"games_lost": "4",
"goals_for": "60",
"goals_against": "45"
"points": "65"
},
{
"position": "...",
"team_name": "...",
"games_played": "...",
"games_won": "...",
"games_drawn": "...",
"games_lost": "...",
"goals_for": "...",
"goals_against": "..."
"points": "..."
}
],
"referrer_url": "https://www.soccertables.com",
}
理想情况下,我希望它的输出是一个平面的关系表:
table_name 位置 team_name games_played 等等... table_1 1 利物浦 29 ... table_1 2 人。城市 29 ... table_2 1 拜仁慕尼黑 29 ... ....
我知道如果我只对 table_1 感兴趣,我可以这样做:
SELECT v.value:position::NUMBER POSITION
, v.value:team_name::STRING TEAM_NAME
, v.value:games_played::NUMBER GAMES_PLAYED
, ...
FROM JSON_TABLE a1, LATERAL FLATTEN(JSON_DATA:table_1) v
并且我可以对 table_2 执行相同的操作并将它们合并,但是对于 table_N 占位符可以有 N 种可能性。
我曾多次看过 LATERAL FLATTEN :
SELECT v.value:position::NUMBER POSITION
, v.value:team_name::STRING TEAM_NAME
, v.value:games_played::NUMBER GAMES_PLAYED
, ...
FROM JSON_TABLE a1, LATERAL FLATTEN(JSON_DATA:table_1) v, LATERAL FLATTEN(JSON_DATA:table_2) v2
但这会导致数据重复,并且不允许我将每个表的列全部放在一个关系结构中。
我确定我在这里缺少一些简单的东西,但我已经到了一个地步,我认为我已经盯着这个太久了,只是看不到它。
在此先感谢,
解决方案
如果您尝试创建 table_n 数据的单个扁平视图,以及第一级的属性,那么这样的事情会起作用。
WITH x AS (
SELECT '{
"dom_url": "https://www.soccertables.com/european_tables",
"event_id": "01b2722a-d8e6-4f67-95d0-8dd7ba088a4a",
"event_utc_time": "2020-05-11 09:01:14.821",
"ip_address": "125.238.134.96",
"table_1": [
{
"position": "1",
"team_name": "Liverpool",
"games_played": "29",
"games_won": "26",
"games_drawn": "2",
"games_lost": "1",
"goals_for": "75",
"goals_against": "35",
"points": "80"
},
{
"position": "2",
"team_name": "Man. City",
"games_played": "29",
"games_won": "20",
"games_drawn": "5",
"games_lost": "4",
"goals_for": "60",
"goals_against": "45",
"points": "65"
},
{
"position": "...",
"team_name": "...",
"games_played": "...",
"games_won": "...",
"games_drawn": "...",
"games_lost": "...",
"goals_for": "...",
"goals_against": "...",
"points": "..."
}
],
"table_2": [
{
"position": "1",
"team_name": "Bayern Munich",
"games_played": "29",
"games_won": "26",
"games_drawn": "2",
"games_lost": "1",
"goals_for": "75",
"goals_against": "35",
"points": "80"
},
{
"position": "2",
"team_name": "Bayer Leverkussen",
"games_played": "29",
"games_won": "20",
"games_drawn": "5",
"games_lost": "4",
"goals_for": "60",
"goals_against": "45",
"points": "65"
},
{
"position": "...",
"team_name": "...",
"games_played": "...",
"games_won": "...",
"games_drawn": "...",
"games_lost": "...",
"goals_for": "...",
"goals_against": "...",
"points": "..."
}
],
"referrer_url": "https://www.soccertables.com",
}' as var)
SELECT
parse_json(x.var):dom_url::string,
parse_json(x.var):event_id::string,
parse_json(x.var):event_utc_time::string,
parse_json(x.var):ip_address::string,
x3.value:games_drawn::string,
x3.value:games_lost::string,
x3.value:games_played::string,
x3.value:games_won::string,
x3.value:goals_against::string,
x3.value:goals_for::string,
x3.value:points::string,
x3.value:position::string,
x3.value:team_name::string
FROM x
,LATERAL FLATTEN(parse_json(x.var)) x2
,LATERAL FLATTEN(X2.VALUE) x3;
CTE 显然只是为了展示您提供的示例 JSON 示例。如果您关心哪些记录来自哪个表,您还可以将x2.key
其作为元素包含在SELECT
.