如何将函数应用于数组列的每个元素? [英] How can I apply a function to each element of a array column?
本文介绍了如何将函数应用于数组列的每个元素?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据集,其中有一个列,其中有一个对象数组,如下所示:
ID TAGS
1 {"tags": [{"tag": "a"}, {"tag": "b"}]}
2 {"tags": [{"tag": "c"}, {"tag": "d"}]}
我希望提取数组中每个元素的tag
字段,因此最终结果为:
ID TAGS
1 ["a","b"]
2 ["c","d"]
假设下表t1
:
CREATE OR REPLACE TEMPORARY TABLE t1 AS (
select 1 as ID , PARSE_JSON('{"tags": [{"tag":"a"}, {"tag":"b"}]}') AS PAYLOAD
UNION ALL
select 2, PARSE_JSON('{"tags": [{"tag":"c"}, {"tag":"d"}]}')
);
sql
纯推荐答案方法是将LATERAL FLATTEN和ARRAY_AGG这样组合:
with t2 as (
select ID, t2.value:tag as tag
from t1, LATERAL FLATTEN(input => payload:tags) t2
)
select t2.id, ARRAY_AGG(t2.tag) as tags from t2
group by ID
order by ID ASC;
T2本身将变为:
ID TAG
1 "a"
1 "b"
2 "c"
2 "d"
在GROUP BY ID
之后变为:
ID TAGS
1 [ "a", "b" ]
2 [ "c", "d" ]
这篇关于如何将函数应用于数组列的每个元素?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文