crate / crate/sqlalchemy-cratedb
SQLAlchemy: Investigate whether and how to build upon the `JSON` type implementation
- Dominant language
- Python
- Stars
- 9
- Forks
- 4
- Avg merge
- 6d 12h
- Merged PRs (30d)
- 3
Description
## 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`](https://github.com/sqlalchemy/sqlalchemy/blob/rel_2_0_17/lib/sqlalchemy/sql/sqltypes.py#L2161-L2725) 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](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.JSON).
> 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`](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.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. [...]
>
> See section »Casting JSON Elements to Other Types« at the [SQLAlchemy JSON type](https://docs.sqlalchemy.org/en/20/core/type_basics.html#sqlalchemy.types.JSON) documentation.
### Code examples
When adhering to the new interface, the same statement would be written slightly different.
#### Before
```python
sa.select(Character).where(Character.data['x'] == 1)
```
#### After
```python
sa.select(Character).where(Character.data['x'].as_integer() == 1)
```
Contributor guide
Assessment
This issue has not been assessed yet.