How to query on a JSON-type field with SQLAlchemy?

Question

This is just a simple example.

field = [[1,12,6], [2,12,8]]

I think about storing this in a json-typed field with SQLAlchemy in sqlite3 database.

But how can I query on this with SQLAlchemy (not SQL)? e.g.

• rows where len(field) is 3
• rows where field[1][2] is not 12
• rows where len(field[*]) is not 3
• group by field[2]
• sum field[1] where field[0] is 1

Is it even possible?

Maybe it would be easier to implent this with a classical many-to-many table (incl. a link-table) instead of json-field?

Answer 1

According to the official documentation, these are the available methods SQLAlchemy provides for JSON Fields.

JSON provides several operations:

Index operations:

data_table.c.data['some key']

Index operations returning text (required for text comparison):

data_table.c.data['some key'].astext == 'some value'

Index operations with a built-in CAST call:

data_table.c.data['some key'].cast(Integer) == 5

Path index operations:

data_table.c.data[('key_1', 'key_2', ..., 'key_n')]

Path index operations returning text (required for text comparison):

data_table.c.data[('key_1', 'key_2', ..., 'key_n')].astext == \
'some value'

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSON

From what I know about the JSON type in PostgreSQL, it's best used only if you would want the whole JSON object. If you want to make SQL like operations on fields of the JSON object, then it's best to use classic SQL relations. Here's one source I found saying the same thing, but there are many more. http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/