Join the Stack Overflow Community
Stack Overflow is a community of 6.9 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

This has been a tricky one, hope someone out there can help us all out by posting a/their method of creating an index on a nested key of a JSON (or JSONB) column in PostgreSQL using SQLAlchemy (i'm specifically using Flask-SQLAlchemy, but I do not think that will matter much for the answer).

I've tried all sorts of permutations of the index creations below and get everything from key errors, to 'c' is not an attribute, to that the operator 'getitem' is not supported on this expression.

Any help would be greatly appreciated.

# Example JSON, the nested property is "level2_A"
{
    'level1': { 
        'level2_A': 'test value', 
    } 
}

class TestThing(db.Model):
    __tablename__ = 'test_thing'

    id = db.Column(db.BigInteger(), primary_key=True)
    data = db.Column(JSONB)

    __table_args__ = (db.Index('ix_1', TestThing.data['level1']['level2_A']), 
            db.Index('ix_2', data['level1']['level2_A'].astext), 
            db.Index('ix_3', "TestThing.c.data['level1']['level2_A'].astext"), 
            db.Index('ix_4', TestThing.c.data['level1']['level2_A'].astext), 
            db.Index('ix_5', "test_thing.c.data['level1']['level2_A']"), 
                      )

# db.Index('ix_1', TestThing.data['level1']['level2_A'])
# db.Index('ix_2_t', "test_thing.data['level1']['level2_A']")
# db.Index('ix_3', "TestThing.c.data['level1']['level2_A'].astext")
# db.Index('ix_4', TestThing.c.data['level1']['level2_A'].astext)
# db.Index('ix_5', "test_thing.c.data['level1']['level2_A']")
share|improve this question
    
Have you tried providing default data in the column? If you're getting key errors, it might be because SQLAlchemy is trying to index data that doesn't exist and is getting confused. – jumbopap Jan 9 '16 at 19:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.