使用ORM关联两表，根据ID数组获取关联表详情并结构化输出的问题

需求 #

使用ORM将event表与event_category表关联：event表的event_category_ids列是ID数组，需要从event_category表获取每个ID的详情，按category分组后以结构化的JSON数组格式返回，最终结果行数与event表一致。

表结构 #

event表 #

event_category表 #

期望输出 #

问题分析 #

你之前的代码核心问题：

• 未将lateral查询与主表event绑定，导致cat_grouped是对所有ID的全局聚合，而非按单条event行单独聚合
• 缺少与主表的最终关联逻辑，无法保证结果行数与主表匹配

修正后的实现代码 #

假设已定义SQLAlchemy模型类：

from sqlalchemy import func, cast, BigInteger, JSONB
from sqlalchemy.orm import Session
from your_models import Event, EventCategory  # 替换为实际模型路径

def get_events_with_structured_categories(session: Session):
    # 1. 展开每个event的ID数组，关联子表获取分类详情，保留event标识
    cat_details = (
        session.query(
            Event.eventid,
            EventCategory.category,
            EventCategory.subcategory
        )
        .select_from(Event)
        .join(
            func.jsonb_array_elements_text(cast(Event.event_category_ids, JSONB)).alias("cat_id"),
            isouter=False
        )
        .join(
            EventCategory,
            EventCategory.event_category_id == cast(func.jsonb_array_elements_text.c.value, BigInteger)
        )
    ).subquery()

    # 2. 按eventid+category分组，聚合子分类为数组，生成单分类结构化对象
    grouped_category_items = (
        session.query(
            cat_details.c.eventid,
            func.json_build_object(
                "category", cat_details.c.category,
                "sub_categories", func.json_agg(func.distinct(cat_details.c.subcategory))
            ).label("category_item")
        )
        .group_by(cat_details.c.eventid, cat_details.c.category)
    ).subquery()

    # 3. 按eventid聚合所有分类对象，关联主表返回完整字段
    final_result = (
        session.query(
            Event.eventid,
            Event.event_category_ids,
            func.json_agg(grouped_category_items.c.category_item).label("categories")
        )
        .join(grouped_category_items, Event.eventid == grouped_category_items.c.eventid)
        .group_by(Event.eventid, Event.event_category_ids)
        .all()
    )
    return final_result

代码逻辑说明 #

1. 第一步通过jsonb_array_elements_text拆分每个event的ID数组，关联子表拿到对应分类信息，同时保留eventid作为后续分组的关联标识
2. 第二步按eventid和category分组，把同一分类下的子分类聚合成数组，生成符合要求的单分类结构化对象
3. 第三步再次按eventid聚合所有分类对象，得到最终的categories数组，同时关联主表返回原始的event字段，确保结果行数与主表一致

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