how can i query data filtered by a JSON Column in SQLAlchemy?
Solution 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")
Solution 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)
mehdy
I started playing with code when I was 10 and I’ve been enchanted with software since then. I am super-passionate about software engineering, startups, and leadership. I spend a lot of time reading books and articles, watching tech talks, and experimenting on these subjects.
Updated on July 13, 2022Comments
-
mehdy almost 2 years
I'm writing an app using Flask and flask-SQLAlchemy.
I have a
models.py
file as followfrom 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 theirdata
field contains an object like this{"type": "anything", "value": "what I want"}
in the list of objects it has. -
Stew about 7 yearsDocumentation for this can be found here: docs.sqlalchemy.org/en/latest/dialects/…
-
M.Void about 2 yearsMaybe this answer will be useful: stackoverflow.com/a/53196494/6077223