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']")