sqlalchemy.types.JSON

Represent a SQL JSON type.

_types.JSON is part of the Core in support of the growing popularity of native JSON datatypes.

The _types.JSON type stores arbitrary JSON format data, e.g.:

data_table = Table('data_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', JSON)
)

with engine.connect() as conn:
    conn.execute(
        data_table.insert(),
        data = {"key1": "value1", "key2": "value2"}
    )

JSON-Specific Expression Operators

The _types.JSON datatype provides these additional SQL operations:

• Keyed index operations:

  data_table.c.data['some key']
  

• Integer index operations:

  data_table.c.data[3]
  

• Path index operations:

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

• Data casters for specific JSON element types, subsequent to an index or path operation being invoked:

  data_table.c.data["some key"].as_integer()
  

  New in version 1.3.11.

Additional operations may be available from the dialect-specific versions of _types.JSON, such as sqlalchemy.dialects.postgresql.JSON and sqlalchemy.dialects.postgresql.JSONB which both offer additional PostgreSQL-specific operations.

Casting JSON Elements to Other Types

Index operations, i.e. those invoked by calling upon the expression using the Python bracket operator as in some_column['some key'], return an expression object whose type defaults to _types.JSON by default, so that further JSON-oriented instructions may be called upon the result type. However, it is likely more common that an index operation is expected to return a specific scalar element, such as a string or integer. In order to provide access to these elements in a backend-agnostic way, a series of data casters are provided:

• .JSON.Comparator.as_string - return the element as a string
• .JSON.Comparator.as_boolean - return the element as a boolean
• .JSON.Comparator.as_float - return the element as a float
• .JSON.Comparator.as_integer - return the element as an integer

These data casters are implemented by supporting dialects in order to assure that comparisons to the above types will work as expected, such as:

# integer comparison
data_table.c.data["some_integer_key"].as_integer() == 5

# boolean comparison
data_table.c.data["some_boolean"].as_boolean() == True

from sqlalchemy import cast, type_coerce
from sqlalchemy import String, JSON
cast(
    data_table.c.data['some_key'], String
) == type_coerce(55, JSON)

data_table.c.data['some_key'].as_integer() == 5

Detecting Changes in JSON columns when using the ORM

The _types.JSON type, when used with the SQLAlchemy ORM, does not detect in-place mutations to the structure. In order to detect these, the sqlalchemy.ext.mutable extension must be used. This extension will allow "in-place" changes to the datastructure to produce events which will be detected by the unit of work. See the example at .HSTORE for a simple example involving a dictionary.

Support for JSON null vs. SQL NULL

When working with NULL values, the _types.JSON type recommends the use of two specific constants in order to differentiate between a column that evaluates to SQL NULL, e.g. no value, vs. the JSON-encoded string of "null". To insert or select against a value that is SQL NULL, use the constant .null:

from sqlalchemy import null
conn.execute(table.insert(), json_value=null())

To insert or select against a value that is JSON "null", use the constant _types.JSON.NULL:

conn.execute(table.insert(), json_value=JSON.NULL)

The _types.JSON type supports a flag :paramref:`_types.JSON.none_as_null` which when set to True will result in the Python constant None evaluating to the value of SQL NULL, and when set to False results in the Python constant None evaluating to the value of JSON "null". The Python value None may be used in conjunction with either _types.JSON.NULL and .null in order to indicate NULL values, but care must be taken as to the value of the :paramref:`_types.JSON.none_as_null` in these cases.

Customizing the JSON Serializer

The JSON serializer and deserializer used by _types.JSON defaults to Python's json.dumps and json.loads functions; in the case of the psycopg2 dialect, psycopg2 may be using its own custom loader function.

In order to affect the serializer / deserializer, they are currently configurable at the _sa.create_engine level via the :paramref:`_sa.create_engine.json_serializer` and :paramref:`_sa.create_engine.json_deserializer` parameters. For example, to turn off ensure_ascii:

engine = create_engine(
    "sqlite://",
    json_serializer=lambda obj: json.dumps(obj, ensure_ascii=False))