CODEWITHSUNDEEP
jsonsqlitesqlalchemy
buhtz
buhtz

Reputation: 12152

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

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.

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?

Upvotes: 11

Views: 10988

Answers (1)

ApolloFortyNine
ApolloFortyNine

Reputation: 590

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/

Upvotes: 9

Related Questions