ydb-platform / ydb-sqlalchemy

YQL Dialect for SQLAlchemy
Apache License 2.0
18 stars 5 forks source link

Add basic JSON support #28

Closed LuckySting closed 5 months ago

LuckySting commented 5 months ago

Problem

All modern SQL databases implements JSON type field. It supports special column type and a batch of special operation with JSON such as indexing and casting. SQLAlchemy also supports JSON field and operations with it, so it needs to be implemented in dialect.

In this PR

In this PR the basic JSON type is implemented, also I created a YQLJson type for future extending with special YQL specific things such as Yson::Equals.

Example

This SQLAlchemy statement

sa.select(table.c.json_field[('my', 'list', 5)]).where(table.c.json_field['key'].as_string() == 'test')

converts to

SELECT Yson::ConvertTo(Yson::Ypath(json_field, '/my/list/5'), Optional<Json>))
FROM table
WHERE 
    Yson::ConvertTo(json_field['key'], Optional<Utf8>)) == 'test'