How can I query data filtered by a JSON column in SQLAlchemy?

Question

I have a flash application using flask-SQLAlchmey. I have a db model like this

from sqlalchemy.dialects.postgresql import JSON from flask.ext.sqlalchemy import SQLAlchemy 

...

 db = SQLAlchemy() 

...

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

Data field

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

I want to query all user objects in which the data contains {"type": "anything", "value": "what I want"} .

Answer 1

I found it with cast :

 from sqlalchemy.types import Unicode # codes... Custom.query.filter(Custom.data['value'].astext.cast(Unicode) == "what I want") 

Answer 2

Assuming your table is called "custom" and your json field is called "data", the following sql statement will get your results, where the subfield of the value is "what I want".

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