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?

wouter bolsterlee · Accepted Answer · 2015-08-06 13:12:05Z

6

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()

edited Aug 6, 2015 at 13:12

answered Aug 4, 2015 at 10:53

wouter bolsterlee

4,06725 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jony cruse Over a year ago

I tried but I got error.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) operator class "text_pattern_ops" does not accept data type jsonb

bjt38 Over a year ago

Using the version with "postgresql_ops" works for me - in tandem with a manual migration with Alembic. If I use the "text" wrapper, I get an error: Index 'xyz' is against table 'abc', and cannot be associated with table 'abc'.

Matjam · Accepted Answer · 2021-03-18 17:26:02Z

5

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.

answered Mar 18, 2021 at 17:26

Matjam

1342 silver badges5 bronze badges

Collectives™ on Stack Overflow

How to create jsonb index using GIN on SQLAlchemy?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related