英文:
AWS Athena Search for Substring in array using contains() method
问题
我有一个包含多个元素的数组列。我想查询这些数组，看看它们的任何元素是否包含特定的子字符串。
我已经尝试过使用 contains() 方法来实现这一点，它对于精确匹配有效。但我希望能够类似地处理子字符串。
英文:
I have a column of arrays that are populated with multiple elements. I would like to query these arrays to see if any of their elements contain a specific substring.
I have tried using the contains() method for this, which works for exact matches. But I would like to return something similar for a substring.
答案1
得分: 1
你可以使用 any_match 函数：
-- 示例数据
WITH dataset(str_arr) AS (
VALUES (array['Test1', 'foo', 'bar']),
(array['foo1']),
(array['42']),
(array[])
)
-- 查询
SELECT *,
any_match(str_arr, r -> r like '%1%')
FROM dataset;
输出：
| str_arr | _col1 |
|---|---|
| [Test1, foo, bar] | true |
| [foo1] | true |
| [42] | false |
| [] | false |
或者使用 filter + cardinality 的组合，如果 any_match 不可用：
-- 查询
SELECT *,
cardinality(filter(str_arr, r -> r like '%1%')) > 0
FROM dataset;
英文:
You can use any_match:
-- sample data
WITH dataset(str_arr) AS (
VALUES (array['Test1', 'foo', 'bar']),
(array['foo1']),
(array['42']),
(array[])
)
-- query
SELECT *,
any_match(str_arr, r -> r like '%1%')
FROM dataset;
Output:
| str_arr | _col1 |
|---|---|
| [Test1, foo, bar] | true |
| [foo1] | true |
| [42] | false |
| [] | false |
Or using combination of filter + cardinality if the any_match is not available:
-- query
SELECT *,
cardinality(filter(str_arr, r -> r like '%1%')) > 0
FROM dataset;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论