Postgresql - Index on a json field

[Phillip Aquilina]

Using postgresql, I have a JSON type column. My understanding from their docs was that only jsonb columns could have an index created on them (a feature of postgresql 9.4) but then I found an SO answer that said otherwise. I haven't had the chance to test it since I'm away from my dev environment, but the sqlalchemy docs seem to support this idea, mentioning the availability of "Index operations."

Unless I'm missing something obvious (very possible), it seems like this can be done through sql, but is there a way to create an index on a json field through the sqlalchemy api? I can't seem to find a way to do this.

Thanks,

Phil

[Mike Bayer]

SQLAlchemy's API allows CREATE INDEX via the Index construct: http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes

--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Phillip Aquilina]

Thanks for replying. I've read through that doc and I still don't see how that addresses my question. Is there somewhere in there that describes how to create an index on a json field?  It seems like to me it's simple to create an index on a column but this would be creating an index on nested data inside the column.

- Phil

[Mike Bayer]

I'm not familiar with any other style of index for this column type.   If you can show me at http://www.postgresql.org/docs/9.4/static/datatype-json.html or wherever what specific DDL you're looking for, you can simply emit it using engine.execute("<ddl>").

[Mike Bayer]

per the SO answer, you're looking for "CREATE INDEX ON publishers((info->>'name'));".  Either you can emit this directly as a string, or use Index, just as it states:

from sqlalchemy import create_engine, Integer, Index, Table, Column, MetaData
from sqlalchemy.dialects.postgresql import JSON

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m = MetaData()
publishers = Table('publishers', m, Column('id', Integer), Column('info', JSON))
Index("foo", publishers.c.info['name'].astext)

m.create_all(e)

output:

CREATE TABLE publishers (
    id INTEGER,
    info JSON
)


CREATE INDEX foo ON publishers ((info ->> 'name'))

[Phillip Aquilina]

Ah! I'll give that a try. Thanks Mike.

[Phillip Aquilina]

This worked as described. Thanks again. I have a followup question.  It doesn't seem like there's an analog to table.create(checkfirst=True) for an Index.  I found this issue that seems to mention having this functionality but it doesn't look like it's been implemented? Is there a normal workaround for this?

[Mike Bayer]

On 7/2/14, 11:38 AM, Phillip Aquilina wrote:

This worked as described. Thanks again. I have a followup question.  It doesn't seem like there's an analog to table.create(checkfirst=True) for an Index.  I found this issue that seems to mention having this functionality but it doesn't look like it's been implemented? Is there a normal workaround for this?

typically Index is bundled with its parent Table, and the conditional aspect of it comes from the Table being created conditionally.    Otherwise, if the Index is added after the fact, typically people are using migration tools to get that so that's where the conditional aspect comes in.   So the case where Index.create() really needs conditional behavior is slim.    You can for now use inspector:

from sqlalchemy import inspect
insp = inspect(engine)
for idx in insp.get_indexes('tablename'):
    if idx['name'] == 'myname':
       break
else:
    Index('myname', x, y, z).create(engine)

[Phillip Aquilina]

Perfect thanks Mike.

[Phillip Aquilina]

I finally had a chance to try this with the inspector and quickly discovered it doesn't support expression-based indexes with this warning, "Skipped unsupported reflection of expression-based index <some_index>."  I can patch this out locally for my own needs but is there a bigger reason this is not yet supported that I should be worried about?

[Mike Bayer]

On 7/5/14, 3:14 PM, Phillip Aquilina wrote:
> I finally had a chance to try this with the inspector and quickly
> discovered it doesn't support expression-based indexes with this
> warning, "Skipped unsupported reflection of expression-based index
> <some_index>." I can patch this out locally for my own needs but is
> there a bigger reason this is not yet supported that I should be
> worried about?

on the reflection side, expressions inside of indexes (which is largely
a Postgresql thing) are skipped at the moment. This doesn't present any
issues. It only means if you're using Alembic to autogenerate
migrations, you might have to type these expressions into your migration
file manually.

[Phillip Aquilina]

Great, thanks again for the replies and thanks for the awesome tool.

[koc...@gmail.com]

Hi Michael, I'm trying to adapt your answer above to declare indexes inside the __table_args__ tuple in my model class. However, when I run the alembic command to generate the migration script, the JSONB column indexes are not generated.

I posted a SO question with more details, perhaps you could help me understand what I'm missing in the index generation

http://stackoverflow.com/questions/39190935/jsonb-field-indexes-in-sqlalchemy-declarative-mappers

Thanks in advance,

Shailesh

[Mike Bayer]

On 08/28/2016 07:53 AM, koc...@gmail.com wrote:
> Hi Michael, I'm trying to adapt your answer above to declare indexes
> inside the __table_args__ tuple in my model class. However, when I run
> the alembic command to generate the migration script, the JSONB column
> indexes are not generated.
>
> I posted a SO question with more details, perhaps you could help me
> understand what I'm missing in the index generation
> http://stackoverflow.com/questions/39190935/jsonb-field-indexes-in-sqlalchemy-declarative-mappers

these are functional indexes which are not supported by Alembic's schema
comparison feature right now. In fact you should see warning that
states "autogenerate skipping functional index %s; not supported by
SQLAlchemy reflection". Just write them out manually in your Alembic
script.

>
>
> Thanks in advance,
> Shailesh
>
>
> On Tuesday, 1 July 2014 10:53:13 UTC+5:30, Michael Bayer wrote:
>
> per the SO answer, you're looking for "CREATE INDEX ON
> publishers((info->>'name'));". Either you can emit this directly as
> a string, or use Index, just as it states:
>
> from sqlalchemy import create_engine, Integer, Index, Table, Column,
> MetaData
> from sqlalchemy.dialects.postgresql import JSON
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
>
> m = MetaData()
> publishers = Table('publishers', m, Column('id', Integer),
> Column('info', JSON))
> Index("foo", publishers.c.info

> <http://publishers.c.info>['name'].astext)

>>>> <http://stackoverflow.com/questions/17807030/how-to-create-index-on-json-field-in-postgres-9-3> that

>>>> said otherwise. I haven't had the chance to test it since
>>>> I'm away from my dev environment, but the sqlalchemy docs
>>>> seem to support this idea

>>>> <http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON>,

>>>> mentioning the availability of "Index operations."
>>>>
>>>> Unless I'm missing something obvious (very possible), it
>>>> seems like this can be done through sql, but is there a way
>>>> to create an index on a json field through the sqlalchemy
>>>> api? I can't seem to find a way to do this.
>>>>
>>>> Thanks,
>>>> Phil
>>>> --
>>>> 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
>>>> http://groups.google.com/group/sqlalchemy

>>>> <http://groups.google.com/group/sqlalchemy>.

>>>> For more options, visit https://groups.google.com/d/optout

>>>> <https://groups.google.com/d/optout>.

>>>
>>> --
>>> 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 <javascript:>.

>>> To post to this group, send email to sqlal...@googlegroups.com

>>> <javascript:>.

>>> Visit this group at http://groups.google.com/group/sqlalchemy

>>> <http://groups.google.com/group/sqlalchemy>.

>>> For more options, visit https://groups.google.com/d/optout

>>> <https://groups.google.com/d/optout>.

>>
>> --
>> 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 <javascript:>.

>> To post to this group, send email to sqlal...@googlegroups.com

>> <javascript:>.

>> Visit this group at http://groups.google.com/group/sqlalchemy

>> <http://groups.google.com/group/sqlalchemy>.

>> For more options, visit https://groups.google.com/d/optout

>> <https://groups.google.com/d/optout>.

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

> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.