How to filterby with nested jsonb with sqlalchemy and postgres

Question

I can't work out how to use SQLAlchemy to query a table that contains JSONB data.

The JSON structure is similar to this:

example = {
  "product": "Widget",
  "version": 1,
  "release_date": "2019-07-01T00:00:00.000Z",
  "widget": {
    "code": "ABCDEF",
    "name": "my widget",
    "uses": {
        "rain": "yes",
        "snow": "no",
        "heat": "sometimes",
...

I'm trying to execute a query with this functionality:

SELECT
    json  AS acdata
FROM
    aircraft.aircraft
WHERE json -> 'widget' -> 'uses' ->> 'rain' = 'No';

in this form:

widget= session.query(Widget).filter_by(rain='No').first()

I know the rain='No' isn't correct but I can't work out what should go there! The objective is to get the first Widget whose key widget.uses.rain = 'No'

I have this definition for my Widget class

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.dialects.postgresql import JSONB, UUID

class Widget(Base):
    __tablename__ = 'widgets'
    __table_args__ = {'schema' : 'factory'}

    id = Column(UUID(as_uuid=True), primary_key=True)
    json = Column(JSONB, nullable=False)
...

Related: stackoverflow.com/questions/28216125/…

Ilja Everilä
– Ilja Everilä

2019-07-09 06:25:46 +00:00
Commented Jul 9, 2019 at 6:25 — Ilja Everilä
– Ilja Everilä, Commented Jul 9, 2019 at 6:25

Ilja Everilä · Accepted Answer · 2019-07-09 06:22:21Z

2

To access a nested jsonb value use a tuple of keys (path index operation) and astext to convert the value as text:

widget = session.query(Widget).\
    filter(Widget.json["widget", "uses", "rain"].astext == 'No').\
    first()

The resulting query uses the #>> operator for the path index + astext operation. You cannot use filter_by() for this kind of predicate, as it is used for simple equality checks against attributes of a model class.

edited Jul 9, 2019 at 6:22

answered Jul 9, 2019 at 6:02

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

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

1 Comment

The Welsh Dragon Over a year ago

Thank you, that was exactly what i was looking for!

Collectives™ on Stack Overflow

How to filterby with nested jsonb with sqlalchemy and postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related