Presto 数组包含一个喜欢某种模式的元素

问题描述

例如，我表中的一列是一个数组，我想检查该列是否包含一个包含子字符串denied"的元素(所以像denied at 12:00 pm"、denied by admin"这样的元素将所有计数，我相信我将不得不使用喜欢"来识别模式).如何为此编写sql?

For example, one column in my table is an array, I want to check if that column contains an element that contains substring "denied" (so elements like "denied at 12:00 pm", "denied by admin" will all count, I believe I will have to use "like" to identify the pattern). How to write sql for this?

推荐答案

使用 presto 的 数组函数:

Use presto's array functions:

• filter()，返回满足给定条件的元素
• cardinality()，返回数组的大小:

• filter(), which returns elements that satisfy the given condition
• cardinality(), which returns the size of an array:

像这样:

where cardinality(filter(myArray, x -> x like '%denied%')) > 0

这篇关于Presto 数组包含一个喜欢某种模式的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！