Is your feature request related to a problem? Please describe.
There is no way to express ClickHouse's JSON subcolumn access (SELECT payload.severity FROM t) through cc_sqlalchemy.
func.getSubcolumn(col, 'key') is the "obvious" replacment, but it is not equivalent to col.key. The dotted form resolves to a subcolumn read; getSubcolumn reads the whole document and extracts at runtime:
-- SELECT payload.a FROM t
Actions: INPUT : 0 -> payload.a Dynamic : 0
-- SELECT getSubcolumn(payload, 'a') FROM t
Actions: INPUT : 0 -> payload JSON : 0
FUNCTION getSubcolumn(__table1.payload :: 2, 'a'_String :: 1) -> ... Dynamic : 0Running some test queries with my data, I got:
| form |
bytes/row |
total read |
peak memory |
avg time |
payload.a |
25.0 |
1.15 MiB |
4.40 MiB |
5 ms |
getSubcolumn(payload, 'a') |
657.9 |
30.38 MiB |
71.14 MiB |
47 ms |
Describe the solution you'd like
A first-class construct on the dialect, so the correct form is the reachable one. Either a comparator on the JSON sqltype (col['severity']) or an explicit accessor (col.subcolumn('severity')) compiling to <processed column>.<quoted path>.
Preferably allows passing a type_ parameter for casting, as when accessing subcolumns we often want to cast to concrete types.
Describe alternatives you've considered
- Using
func.getSubcolumn which reads the entire blob
- Using
literal_column, which removes the type-safety
Is your feature request related to a problem? Please describe.
There is no way to express ClickHouse's JSON subcolumn access (
SELECT payload.severity FROM t) throughcc_sqlalchemy.func.getSubcolumn(col, 'key')is the "obvious" replacment, but it is not equivalent tocol.key. The dotted form resolves to a subcolumn read;getSubcolumnreads the whole document and extracts at runtime:Running some test queries with my data, I got:
payload.agetSubcolumn(payload, 'a')Describe the solution you'd like
A first-class construct on the dialect, so the correct form is the reachable one. Either a comparator on the
JSONsqltype (col['severity']) or an explicit accessor (col.subcolumn('severity')) compiling to<processed column>.<quoted path>.Preferably allows passing a
type_parameter for casting, as when accessing subcolumns we often want to cast to concrete types.Describe alternatives you've considered
func.getSubcolumnwhich reads the entire blobliteral_column, which removes the type-safety