I think '=' is being shown because by "format json" the statement produced a
JSON value expression rather than a regular
JSON string. Ideally "format json" should not be at the rhs of a select item
and the validator should complain about
that. So there could be a missing check rule in validator.
To avoid producing escaped JSON value user can add "format json" to the inner
items in a nested structure, for example:
json_object('foo': json_object('foo': 'bar'))
produces
{"foo":"{\"foo\":\"bar\"}"}
while
json_object('foo': json_object('foo': 'bar') format json)
produces
{"foo":"{"foo":"bar"}"}
.
Not 100% sure but I recall that by SQL standard "format json" should be
implicitly specified in nested structure but
current Calcite doesn't support that. So one should write it manually.
Best,
Hongze
On Tue, 2022-01-18 at 21:16 +0000, M Singh wrote:
> Hi Stamatis:
> Here is the JIRA ticket - https://issues.apache.org/jira/browse/CALCITE-4989
> Thanks again for your advice.
> On Tuesday, January 18, 2022, 12:25:28 PM EST, M Singh
> <mans2si...@yahoo.com> wrote:
>
> Hi Stamatis:
> Thanks for your quick response.
> I do have a question regarding the tests you have posted - especially the
> last one (Add tests for JSON_OBJECT in
> functions.iq · zabetak/calcite@988c13c) - it shows the response as
> {dept={address={number=128, street=Avenue New York, po=92000}, name=CSD,
> id=1}}
> I am not sure why the key value separator is a '='.
> The complete segment is included below.
>
> Thanks once again.
>
> > select json_object(KEY 'dept' VALUE( |
> > | | json_object(KEY 'id' VALUE(1), KEY 'name' VALUE('CSD'), KEY
> > 'address' VALUE( |
> > | | json_object(KEY 'number' VALUE(128), KEY 'street' VALUE('Avenue New
> > York'), KEY 'po' VALUE(92000))format
> > json) |
> > | | ) format json) |
> > | | ) format json; |
> > | |
> |
> > | |
> > +---------------------------------------------------------------------------------+
> > |
> > | | | EXPR$0 | |
> > | |
> > +---------------------------------------------------------------------------------+
> > |
> > | | | {dept={address={number=128, street=Avenue New York, po=92000},
> > name=CSD, id=1}} | |
>
>
>
>
>
>
>
> On Tuesday, January 18, 2022, 04:42:17 AM EST, Stamatis Zampetakis
> <zabe...@gmail.com> wrote:
>
> Hello,
>
> JSON_OBJECT is indeed the appropriate function for this use-case. I did a
> few tests [1] and it seems that escaped quotes are introduced when nesting
> the calls so most likely it is a bug. Please log a JIRA for this.
>
> Best,
> Stamatis
>
> [1]
> https://github.com/zabetak/calcite/commit/988c13ce1ff551d6e4046a3c027ff298f79971f8
>
> On Mon, Jan 17, 2022 at 3:42 PM M Singh <mans2si...@yahoo.com.invalid>
> wrote:
>
> > Hi Folks:
> > I am trying to create a nested json object using JSON_OBJECT and am
> > getting a json with escaped quotes.
> > I have the following query in sql line :
> > select JSON_OBJECT( KEY 'level1' VALUE( JSON_OBJECT(
> > KEY 'level2' VALUE( JSON_OBJECT(
> > KEY 'level3' VALUE 'val3')
> > ) ) ) ) from (values ('{"a":{"b":2}}')) t(v);
> > And it produces the result:
> >
> > +-------------------------------------------------------------+
> >
> > > EXPR$0 |
> >
> > +-------------------------------------------------------------+
> >
> > > {"level1":"{\"level2\":\"{\\\"level3\\\":\\\"val3\\\"}\"}"} |
> > +-------------------------------------------------------------+
> > Is it possible to create a result which is a proper json object:
> > {"level1":{"level2":{"level3":"val3"}}}
> >
> > If there any other function/udf that I can use, please let me know.
> > Thanks
>
