如何从雪花中的变体列创建子集?

Question

例如，假设我的变体列"xyz"由如下数据组成：

{
"post_new_visits": "Repeat",
"post_new1_week": "Thursday",
"post_new2_appt": "Weekday",
"post_new3_site": "12:50AM",
"post_new4_channel": "5.0",
"pre_new2_appt": "Weekday",
"pre_new3_site": "12:50AM",
"pre_new4_channel": "5.0"
}

我想要上面的变体列中的一个新变体列，它应该只有"post*"键值，如下所示，输出应该是这样的。

{
"post_new_visits": "Repeat",
"post_new1_week": "Thursday",
"post_new2_appt": "Weekday",
"post_new3_site": "12:50AM",
"post_new4_channel": "5.0",
}

有什么方法可以做到这一点吗？

Answer 1

也许你可以展平并重新构建 JSON。例如：

create table tmp ( v variant )
as 
select
parse_json(
'{  "post_new_visits": "Repeat",
"post_new1_week": "Thursday",
"post_new2_appt": "Weekday",
"post_new3_site": "12:50AM",
"post_new4_channel": "5.0",
"pre_new2_appt": "Weekday",
"pre_new3_site": "12:50AM",
"pre_new4_channel": "5.0"}'
)
union all                              
select
parse_json(
'{  "post_new_visits": "New",
"post_new1_week": "Friday",
"post_new2_appt": "Weekday",
"post_new3_site": "13:50AM",
"post_new4_channel": "4.0",
"pre_new2_appt": "Weekday",
"pre_new3_site": "14:50AM",
"pre_new4_channel": "2.0"}'
);                                      


select
OBJECT_AGG(v2.key, v2.value)
from
tmp,
lateral flatten(input => v) v2
where
v2.key like 'post%'
group by
v2.seq;