如何通过SQLAlchemy合并PostgreSQL中的JSON数组为单个数组

针对你的需求——将每行groups列中的JSON数组元素展开并合并为一个统一的JSON数组，可以借助PostgreSQL的JSON函数配合SQLAlchemy实现，具体步骤如下：

前提假设 #

假设你的数据表模型定义如下（以SQLAlchemy ORM为例）：

from sqlalchemy import Column, Integer, JSON
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class YourTable(Base):
    __tablename__ = "your_table"
    id = Column(Integer, primary_key=True)
    groups = Column(JSON)  # 如果是JSONB类型，替换为JSONB

方法1：直接聚合为单个JSON数组 #

使用PostgreSQL的json_array_elements（JSON类型）或jsonb_array_elements（JSONB类型）展开数组，再用json_agg（或jsonb_agg）将所有元素聚合为一个JSON数组：

from sqlalchemy import select, func
from sqlalchemy.orm import Session

# 假设已创建Session实例session
query = select(
    func.json_agg(func.json_array_elements(YourTable.groups)).label("merged_groups")
)
# 执行查询并获取结果
merged_groups = session.execute(query).scalar()

执行后merged_groups就是你预期的合并后JSON数组：

[{"id": 5, "name": "imba"}, {"id": 1, "name": "group1"}, {"id": 3, "name": "group3"}]

如果你的groups列是JSONB类型，只需将代码中的json_array_elements替换为jsonb_array_elements，json_agg替换为jsonb_agg即可。

方法2：先获取拆分后的单个元素再合并 #

如果你需要先得到拆分后的每个元素对象，再自行合并为列表，可以直接查询展开后的元素：

query = select(func.json_array_elements(YourTable.groups).label("group_item"))
results = session.execute(query).scalars().all()
# 转换为Python列表
merged_groups = list(results)

这种方式得到的merged_groups同样是符合预期的Python列表结构。

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