crate / crate/sqlalchemy-cratedb

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

Open
#93 3 comments 0 reactions 0 assignees View on GitHub
data types
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.