如何使用SQLAlchemy 2.0将SQL Server的JSON列展开为多行

针对SQL Server中存储字符串数组的JSON列，要实现OUTER APPLY OPENJSON的行展开逻辑，以下是SQLAlchemy 2.0的具体实现方案：

1. 模型定义 #

首先映射general_info表的模型类：

from sqlalchemy import Column, Integer, String, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import select, func

Base = declarative_base()

class GeneralInfo(Base):
    __tablename__ = 'general_info'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    alias = Column(JSON)

2. 构建查询逻辑 #

利用SQLAlchemy对SQL ServerOPENJSON函数的支持，结合outerapply实现横向连接：

# 初始化数据库连接（替换为实际的连接参数）
engine = create_engine('mssql+pyodbc://<用户名>:<密码>@<服务器>/<数据库>?driver=ODBC+Driver+17+for+SQL+Server')
Session = sessionmaker(bind=engine)
session = Session()

# 定义OPENJSON子查询，解析alias列并提取value字段
openjson_subquery = select(func.openjson(GeneralInfo.alias).c.value.label('alias')).correlate(GeneralInfo)

# 构建主查询，使用outerapply实现OUTER APPLY逻辑
query = select(
    GeneralInfo.id,
    GeneralInfo.name,
    openjson_subquery.c.alias
).select_from(GeneralInfo.outerapply(openjson_subquery))

# 执行查询并获取结果
results = session.execute(query).all()

3. 结果说明 #

执行上述查询后，返回结果与预期完全一致：

关键逻辑说明 #

• func.openjson(GeneralInfo.alias)：调用SQL Server原生的OPENJSON函数，将JSON数组解析为行集，默认返回的value字段对应数组中的每个元素。
• outerapply：对应SQL Server的OUTER APPLY语法，确保即使alias为null的记录也会被保留，不会被过滤掉。

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