How to filter SQLAlchemy JSON column containing array of dicts by dict attribute

[Boncheff]

We have a DB model that contains a 'foo' field which is JSONType

Inside this we store the following data:

foo =>    [{"apps": ["test_app"]}, {"tags": ["test_tag1", "test_tag2"]}]

My question is, how can I, using session.query, select all items that have a test_tag_1 as a tag inside the foo column?

I tried session.query(MyModel).filter(MyModel.foo[0]['tags'].in_('test_tag1')).all() but this results in 

*** NotImplementedError: Operator 'getitem' is not supported on this expression

Is what I am trying to achieve even possible?

[Christopher Lee]

Are you referring to the sqlalchemy-utils JSONType (https://github.com/kvesteri/sqlalchemy-utils)?  It doesn't look like it supports querying at all, only saving Python dicts directly.

If you are using PostgreSQL and the native Json type, you might want to check out:

https://www.compose.io/articles/using-json-extensions-in-postgresql-from-python-2/ 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Simon King]

If you’re using the native JSON or JSONB types in PostgreSQL, SQLAlchemy has some extra support for that type:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB

Simon

[Boncheff]

Thanks for your replies - it is now working as expected 

[himanshu jain]

How did you get it working?
can you give an example please

[Joseph Baniqued]

It's just a simple syntax issue in Boncheff's query. in_ takes a list so it should be:

session.query(MyModel).filter(MyModel.foo[0]['tags'].in_(['test_tag1'])).all()