SqlAlchemy + Postgres - query with @> ANY(ARRAY['["...]']::jsonb[])

Question

I have a postgres table represented in the sql alchemy like

from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ListingDatabaseDocument(Base):
    __tablename__ = 'listing'
    uuid = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True)
    doc = Column(JSONB, nullable=False)

My doc jsonb field looks like

{"name": "SOME_NAME", "features": ["BALCONY", "GARAGE", "ELEVATOR"]}

Now I'd like to get all rows where the doc->'features' array contains "ELEVATOR","GARAGE" - in pure sql I do it like

SELECT * FROM listing
WHERE doc -> 'features' @> ANY(ARRAY['["ELEVATOR","GARAGE"]']::jsonb[])

How to achieve this in SqlAlchemy ? I tried something like

from sqlalchemy.dialects.postgresql import JSONB, ARRAY
from sqlalchemy.sql.expression import cast
from sqlalchemy import any_

return session.query(ListingDatabaseDocument).filter(
    ListingDatabaseDocument.doc['features'].op('@>')(any_(cast(['ELEVATOR','GARAGE'], ARRAY(JSONB))))
).all()

but it's not working. Thanks for help !

snakecharmerb · Accepted Answer · 2023-02-03 09:52:43Z

1

If you enable logging in SQLAlchemy you can see that the SQLAlchemy version of the query is casting differently than the SQL version. Specifically it's casting to JSONB[] twice, whereas in the SQL version the casts are JSONB (implicit - the string is already JSON) and JSONB.

SELECT listing.uuid AS listing_uuid, listing.doc AS listing_doc 
  FROM listing 
  WHERE (listing.doc -> %(doc_1)s) @> ANY (CAST(%(param_1)s::JSONB[] AS JSONB[]))

Rather than trying to fix this, I think the SQL version can be simplified to

SELECT uuid, doc 
  FROM listing 
  WHERE doc -> 'features' @> '["ELEVATOR", "GARAGE"]';

And the SQLAlchemy equivalent would be

s.query(ListingDatabaseDocument)
.filter(
    ListingDatabaseDocument.doc['features'].op('@>')(
        sa.cast(['ELEVATOR', 'GARAGE'], JSONB)
    )
)

edited Feb 3, 2023 at 9:52

answered Dec 17, 2022 at 21:30

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlAlchemy + Postgres - query with @> ANY(ARRAY['["...]']::jsonb[])

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related