Hi, Agree with you the equals seems wrong as well but don't have time to investigate. Contributions are welcomed.
Best, Stamatis On Tue, Jan 18, 2022 at 10:17 PM M Singh <mans2si...@yahoo.com.invalid> 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 >
