Using jsonb_array_elements in sqlalchemy select_from and join

Question

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

You could just use raw SQL? It's the source of tuuth anyway. ORMs are just obfuscation layers. — Erwin Brandstetter, May 25 '23 at 04:01

score 1 · Answer 1 · answered May 25 '23 at 04:06

1

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:

answered May 25 '23 at 04:06

Erwin Brandstetter

605,456
145
1,078
1,228

1

@python_user: Yes, `CROSS JOIN` is equivalent to `[INNER] JOIN ... ON true`. The only (obvious) difference is `SELECT resources.*` vs. `SELECT resources.id, ...` – Erwin Brandstetter May 25 '23 at 09:35

Using jsonb_array_elements in sqlalchemy select_from and join

1 Answers1