如何构建SQLAlchemy查询以聚合Postgres中Key模型的Token与Query ID数据

你需要将同一个用户、券商、账户组合下的token和关联的query_id列表聚合到一起，用Postgres的数组聚合函数结合SQLAlchemy的关联查询就能完美实现这个需求。

先回顾你的场景 #

你的Key模型结构是：

class Key(Model):
    __tablename__ = "keys"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    brokerage_id = Column(Integer, ForeignKey("brokerages.id"))
    account_id = Column(Integer, ForeignKey("accounts.id"))
    key = Column(String(128))
    value = Column(String(128))

目标是把分散的token和query_id记录，聚合为(user_id, brokerage_id, account_id, token, [query_id_1, query_id_2, ...])的格式，对应示例数据要得到：
[(2, 2, 2, 999999999999, [888888, 777777]), (1, 2, 3, 444444444444, [])]

最终可行查询方案 #

这里用SQLAlchemy的表别名区分token记录和query_id记录，结合Postgres的array_agg函数做聚合：

from sqlalchemy.orm import aliased
from sqlalchemy import func, and_
from project.models import Key
from project.extensions import db

# 创建Key表的别名，专门用来筛选token类型的记录
key_token = aliased(Key)

q = db.session.query(
    key_token.user_id,
    key_token.brokerage_id,
    key_token.account_id,
    key_token.value.label('token'),
    # 聚合同一分组下的query_id值为数组
    func.array_agg(Key.value).label('query_ids')
).outerjoin(  # 用outerjoin确保没有query_id的token也能被返回，对应空数组
    Key, and_(
        key_token.user_id == Key.user_id,
        key_token.brokerage_id == Key.brokerage_id,
        key_token.account_id == Key.account_id,
        Key.key == 'query_id'
    )
).filter(
    # 只筛选token类型的记录作为主表
    key_token.key == 'token'
).group_by(
    # 按照token的唯一标识分组，确保每个组合只返回一行
    key_token.user_id,
    key_token.brokerage_id,
    key_token.account_id,
    key_token.value
)

# 执行查询得到目标格式的结果
results = q.all()

关键逻辑解释 #

1. 表别名key_token：因为我们需要同时查询token和query_id两种记录，用别名区分主表（只取token）和关联表（只取query_id），避免字段混淆。
2. outerjoin关联：使用外连接而非内连接，确保即使某个token没有对应的query_id（比如示例中的用户1），也会被保留，此时query_ids会返回空数组[]。
3. func.array_agg聚合：Postgres的array_agg函数会把同一分组下的所有query_id值合并成一个数组，SQLAlchemy会自动映射为Python列表。
4. 分组条件：按照user_id、brokerage_id、account_id和token值分组，保证每个唯一的用户-券商-账户-token组合只生成一行结果。

验证结果 #

执行上述查询后，你会得到完全符合需求的元组列表：
[(2, 2, 2, '999999999999', ['888888', '777777']), (1, 2, 3, '444444444444', [])]

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