英文:
How to filter out certain keys in a map in Trino/Presto?
问题
我有一列，其中每一行都是一个包含键值对的地图，我想要过滤掉一些我不关心的键，以便每一行的结果地图是一个经过过滤的版本。
我关心的键将在一个数组中，并且应该进行排序。我该如何使用这个键的数组以及IN运算符，以便我可以使用map_filter函数，或者是否有其他方法来获取每一行的地图，也许可以使用element_at函数。我在文档中没有找到任何相关信息。
到目前为止，我有以下SQL查询：
WITH temp AS (
SELECT array_sort(filter(map_keys(mapColumn), x -> x not in ('id', 'image')) as myFiltered
FROM myTable WHERE myFilter = 'a'
)
SELECT map_filter(mapColumn, (k, v) -> k in temp.myFiltered) from myTable WHERE myFilter = 'a'
注意：我只提供了SQL查询的翻译，不包括代码部分。
英文:
I have a column which is a map with key value pairs per row and i would like to filter out some keys that I do not care about such that the resultant map is a filtered version per row.
The keys that I care about will be in an array though which should be sorted. How can I use that array of keys along with an IN operator so I can use map_filter function or is there any other way to fetch the map per row using perhaps element_at function. I did not find anything in the documentation.
So far I have this SQL query:
WITH temp AS (
SELECT array_sort(filter(map_keys(mapColumn), x -> x not in ('id', 'image')))) as myFiltered
FROM myTable WHERE myFilter = 'a'
)
SELECT map_filter(mapColumn,(k, v) -> k in temp.myFiltered))) from myTable WHERE myFilter = 'a'
答案1
得分: 0
如果我正确理解您的要求 - 您需要使用 contains 数组函数：
select map_filter(m, (k, v) -> contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);
输出：
filtered
----------
{1=a}
或者，如果您需要筛选掉某些键 - 那么使用 not contains：
select map_filter(m, (k, v) -> not contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);
输出：
filtered
----------
{2=b}
英文:
If I understood your requirement correctly - you need to use contains array function:
select map_filter(m, (k, v) -> contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);
Output:
filtered
----------
{1=a}
Or if you need to filter out some keys - then not contains:
select map_filter(m, (k, v) -> not contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);
Output:
filtered
----------
{2=b}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论