Using sqlalchemy to create an index on a json key (expression index)

Question

The situation is I have a JSON column type in a postgresql table. I'd like to create an index on one or more of the keys in the json. I believe I know how to do this using sql (Side note: I was under the impression that you had to be using jsonb to have this kind of indexing according to the postgresql docs. Doesn't seem like that's the case.)

For consistency, I'd like to do this through sqlalchemy, but I don't see any mention on how to do this in their docs. Is this possible?

Answer 1

I've figured out the answer to this question. As Craig pointed out, this is an expression-based index, which was made possible on the JSON column type in Postgresql 9.3. This can be done through SQLAlchemy in the following manner:

Given a model called MyModel and a JSON column type called data that takes this form:

{"my_key": "my_value"}

To create an expression-based index on my_key, you could do this:

index = Index("my_index_name", MyModel.c.data['my_key'].astext)
index.create(engine)

The original answer to this is from Mike Bayer in the SQLAlchemy google group, which you can find here.