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

Question

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.

Answer 1

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")

Answer 2

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)