求助：将含unnest与ilike的SQL转换为SQLAlchemy语句

需求：查询books表中，categories字符串数组列至少包含一个以fiction开头元素的所有记录。

前提假设 #

假设你已经定义了对应的SQLAlchemy模型：

from sqlalchemy import Column, Integer, String, ARRAY
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    categories = Column(ARRAY(String))
    # 其他业务列...

方法1：对应原SQL的EXISTS+UNNEST实现 #

直接将你提供的原生SQL逻辑转换为SQLAlchemy查询：

from sqlalchemy import select, func, exists

# 构造子查询：展开数组并过滤匹配项
subquery = select(func.unnest(Book.categories).label('categories')).where(
    func.unnest(Book.categories).ilike('fiction%')
).alias('x')

# 主查询：通过EXISTS筛选符合条件的书籍
query = select(Book).where(exists(subquery))

或者更紧凑的写法：

query = select(Book).where(
    exists(
        select(func.unnest(Book.categories)).where(
            func.unnest(Book.categories).ilike('fiction%')
        )
    )
)

方法2：使用PostgreSQL数组ANY操作符（更简洁） #

你提到的简易查询可以修正为合法的PostgreSQL语法，对应SQLAlchemy的实现更简洁：

from sqlalchemy import select, array

# 方式A：直接使用ILIKE ANY数组
query = select(Book).where(Book.categories.ilike(array(['fiction%'])))

# 方式B：使用SQLAlchemy的any()方法
query = select(Book).where(Book.categories.any(ilike='fiction%'))

这两种写法生成的原生SQL类似于：

SELECT books.id, books.categories 
FROM books 
WHERE books.categories ILIKE ANY (ARRAY['fiction%'])

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