SQLAlchemy: Investigate whether and how to build upon the `JSON` type implementation

[amotl]

About

crate/crate-python#559 revealed that the SQLAlchemy implementation for supporting CrateDB's OBJECT type has drawbacks when accessing the attributes of the dictionary facade by key, i.e. indexing into the dictionary using the classical Entity.field["attribute"] notation.

Thoughts

Here, we are trying to outline a possible alternative way of implementing support for that, which may more closely align to PostgreSQL's JSON type support. While CrateDB itself does not provide support for the JSON|JSONB types, the same SQLAlchemy interface could still be leveraged to access CrateDB's OBJECT type in the same spirit. At least, this is what I am assuming for now.

Details

Please inspect SQLAlchemy's sqltypes.JSON type implementation, which is provided as a facade for vendor-specific JSON types, which are currently PostgreSQL, MySQL, SQLite, and Microsoft SQL Server. Each of them has their own specializations, so I think the chance to re-use some code from the generic type variants for implementing CrateDB's OBJECT type might actually be possible.

New interface

The new interface to index operations is based on data casters.

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 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. [...]

See section »Casting JSON Elements to Other Types« at the SQLAlchemy JSON type documentation.

Code examples

When adhering to the new interface, the same statement would be written slightly different.

Before

sa.select(Character).where(Character.data['x'] == 1)

After

sa.select(Character).where(Character.data['x'].as_integer() == 1)

[amotl]

A bit of progress with crate/crate-python#561.

• Switches over to an implementation based on SQLAlchemy's JSON-based type machinery, omitting the marshalling.
• The test suite verified that the behavior did not change on the existing test cases, so I think it's reasonable.
• Both query variants outlined above work, so the change is both backwards-compatible, and improves usability by adhering to standard conventions around accessing dict-like types using the data caster accessor methods outline above.
• Still need to use cache_ok = False on the OBJECT type implementation. Unfortunately, this detail did not change, even when using the data casters instead of pure indexed access by key.

[amotl]

That patch may include more improvements in this area.

• Types: Emulate PostgreSQL's JSON(B) types using CrateDB's OBJECT #27