sqlalchemy.dialects.postgresql.json.JSON

Represent the PostgreSQL JSON type.

_postgresql.JSON is used automatically whenever the base _types.JSON datatype is used against a PostgreSQL backend, however base _types.JSON datatype does not provide Python accessors for PostgreSQL-specific comparison methods such as _postgresql.JSON.Comparator.astext; additionally, to use PostgreSQL JSONB, the _postgresql.JSONB datatype should be used explicitly.

The operators provided by the PostgreSQL version of _types.JSON include:

• Index operations (the -> operator):

  data_table.c.data['some key']
  
  data_table.c.data[5]
  

• Index operations returning text (the ->> operator):

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

  Note that equivalent functionality is available via the .JSON.Comparator.as_string accessor.

• Index operations with CAST (equivalent to CAST(col ->> ['some key'] AS <type>)):

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

  Note that equivalent functionality is available via the .JSON.Comparator.as_integer and similar accessors.

• Path index operations (the #> operator):

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

• Path index operations returning text (the #>> operator):

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

Index operations 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.

Custom serializers and deserializers are specified at the dialect level, that is using _sa.create_engine. The reason for this is that when using psycopg2, the DBAPI only allows serializers at the per-cursor or per-connection level. E.g.:

engine = create_engine("postgresql://scott:tiger@localhost/test",
                        json_serializer=my_serialize_fn,
                        json_deserializer=my_deserialize_fn
                )

When using the psycopg2 dialect, the json_deserializer is registered against the database using psycopg2.extras.register_default_json.