python - How to create jsonb index using GIN on SQLAlchemy? -

here's current code creating index jsonb.

index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') 

but got error.

sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint:  must specify operator class index or define default operator class data type.  [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] 

is there way create index on sqlalchemy?

the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use:

index('my_index', my_table.c.id, my_table.c.data,                         postgresql_ops={                             'data': 'text_pattern_ops',                             'id': 'int4_ops'                         }) 

from experimenting, seems need use text() index description if want specify "operator class" expression index. so,

db.index(     'ix_sample',     sqlalchemy.text("(jsoncol->'values') jsonb_path_ops"),     postgresql_using="gin") 

...in __table_args__ orm model specifies gin index on jsonb field contains array of strings, , allows efficient lookups, i.e. matching on of strings in json array field looks this:

{   "values": ["first", "second", "third"],   "other": "fields",   "go": "here" } 

querying using @> operator in postgresql this:

import sqlalchemy sqlalchemy.dialects import postgresql  query = session.query(mymodel).filter(     sqlalchemy.type_coerce(mymodel.jsoncol['values'], postgresql.jsonb)     .contains(sqlalchemy.type_coerce("second", postgresql.jsonb))) results = query.all()