如何在SQLAlchemy ORM中生成PostgreSQL目标SELECT语句？

一、子查询中生成目标语句 #

你之前的问题有两个核心错误：一是误用func.concat（字符串拼接函数），而PostgreSQL数组拼接需要用运算符||；二是未正确构建子查询结构，直接嵌套func.unnest无法生成select distinct x from ...的逻辑。

正确的SQLAlchemy实现代码如下：

from sqlalchemy import select, func, text

# 假设sm、rm为对应的表对象或别名
subquery = select(func.distinct(text('x')))\
    .select_from(func.unnest(func.op('||', sm.c.setting_platform_ids, rm.c.rule_platform_ids)).alias('x'))

platform_ids_expr = func.array(subquery).label('platform_ids')

这段代码会生成你需要的SQL：

array(SELECT DISTINCT x FROM unnest(sm.setting_platform_ids || rm.rule_platform_ids) AS x) AS platform_ids

二、主查询中生成目标语句 #

主查询需要先对非空数组做聚合，再展开去重后重新聚合为数组，重点是filter条件的正确写法和子查询结构：

# 假设rs为主查询关联的子查询或表别名
agg_expr = func.array_agg(rs.c.platform_ids).filter(rs.c.platform_ids != '{}')

subquery_main = select(func.distinct(text('x')))\
    .select_from(func.unnest(agg_expr).alias('x'))

platform_ids_main_expr = func.array(subquery_main).label('platform_ids')

这段代码会生成目标SQL：

array(SELECT DISTINCT x FROM unnest(array_agg(rs.platform_ids) FILTER (WHERE rs.platform_ids <> '{}')) AS x) AS platform_ids

补充说明 #

• 用func.op('||', a, b)是SQLAlchemy调用PostgreSQL数组拼接运算符的标准方式；
• 嵌套的select distinct必须通过select()对象构建，再传入func.array()中，才能生成正确的嵌套查询结构；
• filter条件直接通过func.array_agg(...).filter(...)添加，SQLAlchemy会自动转换为PostgreSQL的FILTER (WHERE ...)语法。

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