How to create jsonb index using GIN on SQLAlchemy?

Question

Here's current code creating index for JSONB.

Index("mytable_data_idx_id_key", Mytable.data['id'].astext, postgresql_using='gin')

But I got this error.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) data type text has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.
 [SQL: "CREATE INDEX event_data_idx_id_key ON event USING gin ((data ->> 'id'))"]

Is there any way to create index on SQLAlchemy?

Answer 1

The PostgreSQL specific SQLAlchemy docs at http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention a postgresql_ops dictionary to provide the "operator class" used by PostgreSQL, and provide this example illustrating its use:

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

From experimenting, it seems that you need to use a text() index description if you want to specify the "operator class" for an expression index. So,

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

...in __table_args__ for an ORM model specifies a GIN index on a jsonb field that contains an array of strings, and that allows for efficient lookups, i.e. matching on any of the strings in the JSON array field that looks like this:

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

Querying using the @> operator in PostgreSQL would look something like this:

import sqlalchemy
from 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()

Answer 2

I don't know if things have changed in sqlalchemy since the previous accepted answer, but in the code base I work in, this was done with the following:

    __table_args__ = (
        db.Index(
            "index_TABLENAME_on_COLUMN_gin", 
            "COLUMN", 
            postgresql_using="gin",
        ),
    )

This will create the expected GIN index in Postgresql.