如何在SQLAlchemy ORM中正确使用PostgreSQL JSONB jsonb_to_recordset函数

你之前写的代码无法运行的核心原因：jsonb_to_recordset 是PostgreSQL的表值函数（TVF），会返回带指定列结构的多行数据集，不属于普通标量函数，不能直接用func+label的方式构造，也不能跨模型直接访问不存在的虚拟表字段。

具体实现步骤 #

• 第一步：注册带明确返回列定义的jsonb_to_recordset表值函数
  SQLAlchemy 1.4+ 原生支持表值函数的构造，只需要在func调用后链式调用table_valued方法，把你原生SQL中声明的返回列名、列类型、表别名全部绑定即可：

  from sqlalchemy import Text, Boolean, and_
  from sqlalchemy import func
  from your_project.models import LoanInformation  # 你已经定义好的ORM映射模型
  
  # 构造匹配原生SQL结构的表值函数
  record_set_func = func.jsonb_to_recordset(
      LoanInformation.general_info  # 传入需要解析的JSONB类型字段
  ).table_valued(
      # 严格对应原生SQL中定义的返回列结构
      {"name": "name", "type_": Text},
      {"name": "exist_data", "type_": Text},
      {"name": "found", "type_": Boolean},
      # 指定函数结果集的别名，和原生SQL的 items 别名完全一致
      name="items",
      # 较新版本的PostgreSQL加上这个参数，可避免lateral连接语法报错
      lateral=True
  )
  

• 第二步：基于注册好的表值函数构建完整ORM查询
  把函数结果集作为独立的表加入查询的from子句，和主表做交叉连接（对齐你原生SQL中逗号分隔的隐式连接逻辑），再拼接过滤条件：

  query = (
      session.query(
          LoanInformation.title,
          record_set_func.c.name,
          record_set_func.c.exist_data,
          record_set_func.c.found
      )
      .select_from(LoanInformation)
      # 等价于原生SQL中 from 主表, jsonb_to_recordset(...) as items 的连接逻辑
      .join(record_set_func, onclause=True)
      # 对齐原SQL的where过滤条件
      .filter(
          and_(
              LoanInformation.title == "r12",
              record_set_func.c.name == "Investor"
          )
      )
  )
  
  # 执行查询，返回的结果结构和原生SQL查询完全一致
  query_results = query.all()
  

常见报错排查 #

如果你运行时提示“the server does not support lateral join”，只需要删掉上面定义TVF时的lateral=True参数即可，旧版本PostgreSQL不需要这个参数就能执行隐式交叉连接的逻辑。

你之前代码里的两处硬错误：

• 把字符串"items"当成函数调用传入列定义，属于Python语法不允许的写法
• 直接访问LoanInformation.items.name，这个虚拟表不是ORM模型的内置属性，不存在对应的映射关系，自然会抛出属性不存在的错误

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