postgresql 在SQLAlchemy select_from和join中使用JSONB_array_elements

s1ag04yj 于 2023-05-28 发布在 PostgreSQL

关注(0)|答案(2)|浏览(148)

我正在使用SQLAlchemy ORM，并试图弄清楚如何生成一个PostgreSQL查询，如下所示：

SELECT resources.*
  FROM histories, jsonb_array_elements_text(histories.reported_resources) as report_resource_name
  JOIN resources ON resources.resource_name = report_resource_name
 WHERE histories.id = :id

到目前为止，我得到了这个：

query = (
    select([
        Resource
    ])
    .select_from(
        History, 
        func.jsonb_array_elements(History.reported_resources).alias('report_resource_name'))
    .join(Resource, Resource.resource_name == text('report_resource_name'))
    .where(History.id = 1)
)

但错误说：
InvalidRequestError：无法确定要从哪个FROM子句联接，有多个FROMS可以联接到此实体。请使用.select_from（）方法来建立一个显式的左侧，以及提供一个显式的ON子句（如果还没有）来帮助解决歧义。
如何使用SQLAlchemey将资源表连接到jsonb_array_elements结果中的.select_from()？
最小模型表和输入数据如下：

class History(Base):
    __tablename__ = 'histories'
    id = Column(Integer, primary_key=True)
    reported_resources = Column(JSONB) 

class Resource(Base):
    __tablename__ = 'resources'
    id = Column(Integer, primary_key=True)
    resource_name = Column(String)

Resource
id | resource_name
--------
1  | machine1
2  | machine2
3  | operator1
4  | operator4

History
id | reported_resources
-------
1  | ['machine2', 'operator4']

postgresql

来源：https://stackoverflow.com/questions/76328651/using-jsonb-array-elements-in-sqlalchemy-select-from-and-join

2条答案

按热度按时间

cgyqldqp1#

我不太懂ORM方言，但它应该有助于将SQL语句重写为：

SELECT resources.*
FROM   histories
CROSS  JOIN LATERAL jsonb_array_elements_text(histories.reported_resources) AS rr(report_resource_name)
JOIN   resources ON resources.resource_name = rr.report_resource_name
WHERE  histories.id = :id

(The LATERAL关键字是可选的。）
参见：

What does [FROM x, y] mean in Postgres?
PostgreSQL中的LATERAL JOIN和subquery有什么区别？

赞(0）回复(0）举报 2023-05-28

2skhul332#

我只是在转换@Erwin Brandstetter在他的answer中建议的查询。
下面的代码生成了这个查询，我在true上做了一个连接，因为SQLAlchemy没有本机交叉连接（请随时纠正我）

SELECT resources.id, resources.resource_name 
FROM histories JOIN jsonb_array_elements_text(histories.reported_resources) AS anon_1 ON true JOIN resources ON resources.resource_name = anon_1.value 
WHERE histories.id = :id_1

下面是一个在SQLAlchemy 2.0上运行的完整示例。

from sqlalchemy import create_engine, select, func, true
from sqlalchemy.orm import Mapped, DeclarativeBase, Session, mapped_column
from sqlalchemy.dialects.postgresql import JSONB

class Base(DeclarativeBase):
    pass

engine = create_engine("connection string")

class History(Base):
    __tablename__ = "histories"
    id: Mapped[int] = mapped_column(primary_key=True)
    reported_resources: Mapped[list[str]] = mapped_column(JSONB)

class Resource(Base):
    __tablename__ = "resources"
    id: Mapped[int] = mapped_column(primary_key=True)
    resource_name: Mapped[str]

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Resource(resource_name="machine1"))
    session.add(Resource(resource_name="machine2"))
    session.add(Resource(resource_name="operator1"))
    session.add(Resource(resource_name="operator4"))
    session.add(History(reported_resources=["machine2", "operator4"]))
    session.commit()

with Session(engine) as session:
    col = func.jsonb_array_elements_text(History.reported_resources).table_valued(
        "value"
    )
    statement = (
        select(Resource)
        .select_from(History)
        .join(
            col,
            true(),
        )
        .join(Resource, Resource.resource_name == col.c.value)
        .where(History.id == 1)
    )
    for i in session.scalars(statement):
        print(i.id, i.resource_name)

输出量

2 machine2
4 operator4

赞(0）回复(0）举报 2023-05-28

我来回答

postgresql 在SQLAlchemy select_from和join中使用JSONB_array_elements

2条答案

相关问题

热门标签

最新问答