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