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'))
On 7/1/14, 1:14 AM, Mike Bayer wrote:
> 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>").
>
>
> On 6/30/14, 11:02 PM, Phillip Aquilina wrote:
>> 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
>>
>> On Monday, June 30, 2014 6:07:51 PM UTC-7, Michael Bayer wrote:
>>
>> SQLAlchemy's API allows CREATE INDEX via the Index construct:
>>
>> http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes
>>
>> <http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html?highlight=index#indexes>
>>
>>
>> On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
>>> 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
>>>
>>> <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 <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+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit 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+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit 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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
