0
class Main(Base):
    __tablename__ = 'main'
***
    defects_list = Column(JSON)

***

sample of what I have in db defects_list

"[{\"defect_type_id\": 1, \"defect_type\": \"Crack\", \"defect_position\": [82, 624, 114, 696]}, {\"defect_type_id\": 1, \"defect_type\": \"Crack\", \"defect_position\": [84, 1309, 119, 1377]}, {\"defect_type_id\": 1, \"defect_type\": \"Crack\", \"defect_position\": [43, 2853, 116, 3053]}]"

How I can filter all Main objects which have '"defect_type_id": 1'

I want to create a filter for SqlAlchemy Postgres to get all Main objects which has defect_type_id :1 at defects_list

Sviatoslav Kalina
  • 1
  • Your example is a JSON array, and contains several objects which could have different `defect_type_id`. Do you want filtering in this array ? Or on `Main` objects if their arrays has any `defect_type_id = 1` ? only if all `defect_type_id = 1` ? PS. what have you tried so far ? – ljmc Feb 22 '23 at 19:59
  • I want to filter the Main object if it has "defect_type_id :1" in defects_list I understand that defects_list contains multiple objects And want to filter if one of them is defect_type_id = 1 I tried https://stackoverflow.com/questions/53264047/sqlalchemy-filter-by-json-field https://stackoverflow.com/questions/53195944/sqlalchemy-filtering-by-a-key-in-a-json-column https://stackoverflow.com/questions/44220206/sqlalchemy-filter-a-json-column-containing-an-array also tried as_string. json_operators, Raw sql – Sviatoslav Kalina Feb 22 '23 at 20:17
  • @ljmc If I will be able to filter via the first element of the array would be nice - because each defect_list will have only one defect_type_id in two or more objects – Sviatoslav Kalina Feb 22 '23 at 20:20

1 Answers1

0

The solution which works for me

query = query.filter((getattr(Main, 'defects_list').cast(JSONB).contains([{'defect_type_id': 1}]))

Hope it helps someone

van
  • 74,297
  • 13
  • 168
  • 171
Sviatoslav Kalina
  • 1