Question 177198 in PostgreSQL Question posted in
The official documentation can be found here.

Can jsonb_array_elements be used in SQLAlchemy’s select_from and join?

I am using SQLAlchemy ORM and trying to figure out how to produce a PostgreSQL query like the following:

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

So far I got this:

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)
)

But the error says:

InvalidRequestError: Can’t determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Please use the .select_from() method to establish an explicit left side, as well as providing an explicit ON clause if not present already to help resolve the ambiguity.

How can I join the resources table ON the jsonb_array_elements result in .select_from() using SQLAlchemey?

Minimum model table and input data like following:

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']
Tags:

2

Answers


  1. 0

    I am not fluid with the ORM dialect, but it should help to rewrite your SQL statement as:

    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 keyword being optional in this case.)

    See:

    Login or Signup to reply.
  2. 0

    I am just converting the query suggested by @Erwin Brandstetter
     in his answer.

    The following code generates this query, I do a join on true as SQLAlchemy does not have a native cross join (feel free to correct me)

    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

    Here is a complete example running on 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)

    Output

    2 machine2
4 operator4
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search