CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyflask-sqlalchemy
mehdy
mehdy

Reputation: 3374

how can i query data filtered by a JSON Column in SQLAlchemy?

I'm writing an app using Flask and flask-SQLAlchemy.

I have a models.py file as follow

from sqlalchemy.dialects.postgresql import JSON

class Custom(db.Model):
    __tablename__ = 'custom'

    data = db.Column(JSON)

data field's value would be like this:

[
    {"type": "a string", "value": "value string"},
    {"type": "another", "value": "val"},
    ...
]

Now I want to query all Custom objects that their data field contains an object like this {"type": "anything", "value": "what I want"} in the list of objects it has.

Upvotes: 19

Views: 25362

Answers (2)

mehdy
mehdy

Reputation: 3374

According to the documentation, it can be done using cast:

from sqlalchemy.types import Unicode

Custom.query.filter(Custom.data['value'].astext.cast(Unicode) == "what I want")

Upvotes: 28

Joe Love
Joe Love

Reputation: 5972

Assuming that your table is name "custom" and your json field is named "data" the following sql statement will get your results where the value subfield is equal to "what I want".

sql = text("select * from custom where data->>'value'= 'what I want'")
result = db.engine.execute(sql)

Upvotes: 3

Related Questions