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.

mehdy · Accepted Answer · 2020-08-26 08:18:59Z

29

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

edited Aug 26, 2020 at 8:18

answered Apr 24, 2016 at 8:07

mehdy

3,3844 gold badges29 silver badges46 bronze badges

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

2 Comments

Stew Over a year ago

Documentation for this can be found here: docs.sqlalchemy.org/en/latest/dialects/…

M.Void Over a year ago

Maybe this answer will be useful: stackoverflow.com/a/53196494/6077223

Joe Love · Accepted Answer · 2014-12-07 04:06:03Z

3

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)

edited Dec 7, 2014 at 4:06

answered Dec 4, 2014 at 21:20

Joe Love

6,1062 gold badges24 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related