Search code examples
pythonsqlalchemy

Filter on json data with in_() sqlalchemy

I want to filter features(Json field on database) with items = a or b, but here it returns 0, when I use other columns the filter works correctly. It returns correct data with ["a"] or ["b"] too, what is the reason? and what is the solution?

data.filter(Data.id.in_([1,2])) #works

data.filter(Data.features['items'].in_(["a"])) # returns 3

data.filter(Data.features['items'].in_(["b"])) # returns 1

data.filter(Data.features['items'].in_(["a","b"])) # returns 0 I exepect 4
Solution

  • data.filter(Data.features['items'].in_(["a","b"])) returns 0 because data.filter() is looking features with both a and b in the items field.

    if you only want either a or b, you'll need to filter with an or_ operator from sqlalchemy import:

    data.filter(or_(Data.features['items'].in_(["a"]), Data.features['items'].in_(["b"])))