How to find nested value in postgres JSONB using sqlalchemy?

Question

I have a postgres table with JSONB column name entity. The example of json structure is:

{
  "some_key": "some value",
  "properties": {
    "name": ["name_value"],
  }
}

I need to find records by name_value. I can get it by using query:

SELECT entity FROM my_table where entity->'properties' @> {"name": ["name_value"]};

The problem is: I cannot find the way to create that same query using sqlalchemy ORM.

Update: My model is made dynamic, because multiple tables are using same structure just the different table name. Here is the code:

...
Base = declarative_base(engine)

class ForeignBase(AbstractConcreteBase, Base):
    pass

def make_model(name):
    class Entity(ForeignBase):
        __tablename__ = name
        __mapper_args__ = {"polymorphic_identity": name, "concrete": True}
        __table_args__ = {"extend_existing": True}

        id = Column(String, primary_key=True)
        entity = Column(JSONB, nullable=True)
        ...
    configure_mappers()
    return Entity

And then I use 3 functions to initiate my Models and get one I currently need:

def get_model_list():
    tables_names_list = get_table_name_list()
    model_list = [make_model(PREFIX + table_name) for table_name in tables_names_list]
    return model_list

def get_tables_dict():
    return {table.__tablename__: table for table in ForeignBase.__subclasses__()}

def get_table_object(table_name):
    return get_tables_dict().get(table_name)

mtshaikh · Accepted Answer · 2021-04-21 09:54:47Z

1

You can use the following SQLAlchemy expression. The Operator @> is contains() in SQLAlchemy for JSONB columns

Session().query(MyTable).filter(MyTable.entity["properties"].contains({"name": ["name_value"]})).with_entities(MyTable.entity).all()

edited Apr 21, 2021 at 9:54

answered Apr 21, 2021 at 9:02

mtshaikh

6884 silver badges17 bronze badges

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

6 Comments

SarunasCard Over a year ago

Thank you for an answer. Unfortunately it return an empty list []. It also return the same result without with_entities part.

mtshaikh Over a year ago

Print and check the query it is generating. maybe some filter might be missing

SarunasCard Over a year ago

I have tried to print that statement and got SELECT my_table.entity FROM my_table WHERE ((my_table.entity -> %(entity_1)s)) @> %(param_1)s . Seems like it should be ok. Just not sure about those double parentheses.

mtshaikh Over a year ago

the query looks to be ok. The double parentheses are not a problem. Maybe the data is not there in the db?

SarunasCard Over a year ago

The data is in db, I have put that raw select into db shell and it find the records. I have double checked. The same record appears on other queries too. Can it be related to MyTable model configuration some how?

|

Collectives™ on Stack Overflow

How to find nested value in postgres JSONB using sqlalchemy?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related