JSON(B) Support like in Postgres

[Julian Feinauer <j....@pragmaticminds.de>]

Hi all,

we use Postgres a lot and make heavy use of the JSONB datatype [1].
Is there support for something similar in Calcite?
If so, can someone point me to the docs as I’ve not found anything in the list of builtin functions.

There are several reasons why it would be cool for us to have Calcite in Front of postgres to do some query rewriting if necessary but for that we would definitely need support for something which could be transformed to JSONB.

Best
Julian

[1] https://www.postgresql.org/docs/9.5/functions-json.html

Re:JSON(B) Support like in Postgres

[Hongze Zhang <no...@126.com>]

Hi Julian, 


If I remember right, Calcite does not support Postgres's json and jsonb datatype in current version (1.18).
Calcite has built-in JSON support (see CALCITE-2266[1]) similar to what has been implemented in Oracle and MS SQL, It is a earlier version of the whole JSON things described in the SQL standard. For now these functions[2] mainly accepts character datatypes as JSON input, other data types are not supported yet.


I am not so familiar with Postgres's JSON implementation but I think the implementation are wildly different with Calcite's, Some functions have conflict syntax with Calcite's function(E.g. JSON_VALUE).
If you'd like to process JSON using Postgres's syntax, maybe at first you need to change the Parser code of Calcite to support Postgres's json and jsonb Operators, and also disable the built-in JSON_VALUE function then add Postgres's JSON functions (if you want to use Postgres's JSON_VALUE function on Calcite 1.18).


Best,
Hongze


[1] https://issues.apache.org/jira/browse/CALCITE-2266
[2] http://calcite.apache.org/docs/reference.html#json-functions







At 2018-12-29 18:18:59, "Julian Feinauer" <j....@pragmaticminds.de> wrote:
>Hi all,
>
>we use Postgres a lot and make heavy use of the JSONB datatype [1].
>Is there support for something similar in Calcite?
>If so, can someone point me to the docs as I’ve not found anything in the list of builtin functions.
>
>There are several reasons why it would be cool for us to have Calcite in Front of postgres to do some query rewriting if necessary but for that we would definitely need support for something which could be transformed to JSONB.
>
>Best
>Julian
>
>[1] https://www.postgresql.org/docs/9.5/functions-json.html