python - SQLAlchemy: filtering on values stored in nested list of the JSONB field -

lets have model named item, contains jsonb field data. 1 of records has following json object stored there:

{     "name": "hello",     "nested_object": {         "nested_name": "nested"     },     "nested_list": [         {             "nested_key": "one"         },         {             "nested_key": "two"         }     ] } 

i can find record filtering on name field such:

session().query(item).filter(item.data["name"] == "hello") 

i can find record filtering on nested object likewise such:

session().query(item).filter(item.data[("nested_object","nested_name")] == "hello") 

however struggling find way find record filtering on value of item stored within nested list. in other words want find record above if user has provided value "one", , know in key nested_key within nested_list.

is possible achieve sqlalchemy filters available?

sqlalchemy's jsonb type has contains() method @> operator in postgresql. the @> operator used check if left value contains right json path/value entries @ top level. in case

data @> '{"nested_list": [{"nested_key": "one"}]}'::jsonb 

or in python

the_value = 'one'  session().query(item).filter(item.data.contains(     {'nested_list': [{'nested_key': the_value}]} )) 

the method converts python structure suitable json string database.