使用SQLAlchemy构建多表查询：jsonb_build_object需返回null而非{id:null}

问题根源 #

你的查询存在两处关联逻辑错误，同时聚合函数的处理不符合预期：

1. 关联条件错误：
   • join(models.FamilyTypes, models.Family.id == models.Family.family_type_id)：应该关联Family的family_type_id到FamilyTypes的主键，正确条件为models.Family.family_type_id == models.FamilyTypes.id
   • outerjoin(models.Animals, models.Animals.campaign_id == models.Animals.id)：两边都是Animals表，逻辑完全错误，应该是models.Animals.family_id == models.Family.id（关联Animals到对应的Family）
2. 聚合逻辑问题：当outerjoin无匹配数据时，models.Animals.id为null，jsonb_build_object会生成{"id": null}，array_agg会将这个对象聚合为数组（哪怕只有这一个null对象），而coalesce因为数组本身不是null，所以不会替换成你想要的null。

修正方案 #

方案1：过滤null值后聚合（推荐）

通过filter让array_agg只处理Animals.id不为null的情况，无匹配数据时array_agg直接返回null，coalesce就能返回预期的null：

select(
    models.Family.id,  # 原查询选Animals.id会在无匹配时为null，建议选Family的id作为标识
    models.Animals.description,
    models.FamilyTypes.name.label('type'),
    func.coalesce(
        func.array_agg(
            func.jsonb_build_object('id', models.Animals.id)
        ).filter(models.Animals.id.isnot(None)),  # 过滤null的Animals.id
        None
    ).label('animals')
).join(models.FamilyTypes, models.Family.family_type_id == models.FamilyTypes.id)
.outerjoin(models.Animals, models.Animals.family_id == models.Family.id)
.group_by(models.Family.id, models.FamilyTypes.name)

方案2：移除数组中的null对象并转换为空数组为null

如果需要保留非null的聚合结果，同时将仅含{"id": null}的数组转为null，可以用array_remove和nullif组合：

select(
    models.Family.id,
    models.Animals.description,
    models.FamilyTypes.name.label('type'),
    func.coalesce(
        func.nullif(
            func.array_remove(
                func.array_agg(
                    func.jsonb_build_object('id', models.Animals.id)
                ),
                func.jsonb_build_object('id', None)  # 移除{"id": null}对象
            ),
            func.array()  # 空数组
        ),
        None
    ).label('animals')
).join(models.FamilyTypes, models.Family.family_type_id == models.FamilyTypes.id)
.outerjoin(models.Animals, models.Animals.family_id == models.Family.id)
.group_by(models.Family.id, models.FamilyTypes.name)

关键提示 #

优先修正关联条件错误，这是导致Animals.id为null的核心原因之一；方案1更高效，直接过滤无效数据，避免不必要的对象构建和聚合操作。

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