如何在DuckDB中筛选数组结构体字段含指定子串的行

原始数据 #

创建数据的Python代码：

import duckdb
rel = duckdb.sql("""
    FROM VALUES
        ([{'a': 'foo', 'b': 'bta'}]),
        ([]),
        ([{'a': 'jun', 'b': 'jul'}, {'a':'nov', 'b': 'obt'}])
        df(my_col)
    SELECT *
""")

数据展示：

┌──────────────────────────────────────────────┐
│                    my_col                    │
│        struct(a varchar, b varchar)[]        │
├──────────────────────────────────────────────┤
│ [{'a': foo, 'b': bta}]                       │
│ []                                           │
│ [{'a': jun, 'b': jul}, {'a': nov, 'b': obt}] │
└──────────────────────────────────────────────┘

查询需求 #

保留所有满足my_col数组中任意结构体元素的a字段包含子串'bt'的行。

解决方案 #

方法1：使用ANY语法 #

直接通过ANY操作符检查数组中是否存在符合条件的结构体元素：

SELECT *
FROM (
    VALUES
        ([{'a': 'foo', 'b': 'bta'}]),
        ([]),
        ([{'a': 'jun', 'b': 'jul'}, {'a':'nov', 'b': 'obt'}])
        df(my_col)
)
WHERE ANY(my_col).a LIKE '%bt%'

方法2：使用UNNEST结合EXISTS #

将数组拆分为单行后检查是否存在匹配元素：

SELECT *
FROM (
    VALUES
        ([{'a': 'foo', 'b': 'bta'}]),
        ([]),
        ([{'a': 'jun', 'b': 'jul'}, {'a':'nov', 'b': 'obt'}])
        df(my_col)
) t
WHERE EXISTS (
    SELECT 1
    FROM UNNEST(t.my_col) AS elem
    WHERE elem.a LIKE '%bt%'
)

预期输出 #

┌──────────────────────────────────────────────┐
│                    my_col                    │
│        struct(a varchar, b varchar)[]        │
├──────────────────────────────────────────────┤
│ [{'a': foo, 'b': bta}]                       │
│ [{'a': jun, 'b': jul}, {'a': nov, 'b': obt}] │
└──────────────────────────────────────────────┘

内容的提问来源于stack exchange，提问作者ignoring_gravity