Re: [sqlalchemy] Postgresql - Index on a json field

[Phillip Aquilina]

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


On Saturday, July 5, 2014 1:28:34 PM UTC-7, Michael Bayer wrote:
>
>
> 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 
> > ."  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. 
>
>
>

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

Re: [sqlalchemy] Postgresql - Index on a json field

[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 ."  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 Wednesday, July 2, 2014 10:35:02 AM UTC-7, Phillip Aquilina wrote:
>
> Perfect thanks Mike.
>
> On Wednesday, July 2, 2014 10:17:17 AM UTC-7, Michael Bayer wrote:
>>
>>  
>> 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 
>> <https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst>
>>  
>> 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)
>>
>>
>>
>>
>>  
>> On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote: 
>>>
>>> Ah! I'll give that a try. Thanks Mike.
>>>
>>> On Monday, June 30, 2014 10:23:13 PM UTC-7, 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['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("").
>>>>
>>>>
>>>> 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
>>>>>
>>>>>
>>>>> On 6/30/14, 6:21 PM, Phillip Aquilina wrote:
>>>>>  
>>>&

Re: [sqlalchemy] Postgresql - Index on a json field

[Phillip Aquilina]

Perfect thanks Mike.

On Wednesday, July 2, 2014 10:17:17 AM UTC-7, Michael Bayer wrote:
>
>  
> 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 
> <https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst>
>  
> 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)
>
>
>
>
>  
> On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote: 
>>
>> Ah! I'll give that a try. Thanks Mike.
>>
>> On Monday, June 30, 2014 10:23:13 PM UTC-7, 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['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("").
>>>
>>>
>>> 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
>>>>
>>>>
>>>> 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 mi

Re: [sqlalchemy] Postgresql - Index on a json field

[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 
<https://bitbucket.org/zzzeek/sqlalchemy/issue/527/indexcreate-should-take-checkfirst>
 
that seems to mention having this functionality but it doesn't look like 
it's been implemented? Is there a normal workaround for this?

On Tuesday, July 1, 2014 10:03:40 AM UTC-7, Phillip Aquilina wrote:
>
> Ah! I'll give that a try. Thanks Mike.
>
> On Monday, June 30, 2014 10:23:13 PM UTC-7, 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['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("").
>>
>>
>> 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
>>>
>>>
>>> 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.
>>> 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.
>>>
>>>
>>>   -- 
>> 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://gr

Re: [sqlalchemy] Postgresql - Index on a json field

[Phillip Aquilina]

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

On Monday, June 30, 2014 10:23:13 PM UTC-7, 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['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("").
>
>
> 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
>>
>>
>> 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.
>> 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.
>>
>>
>>   -- 
> 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.
>
>
> -- 
> 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.
>
>
>  

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

Re: [sqlalchemy] Postgresql - Index on a json field

[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

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

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

[sqlalchemy] 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

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