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 <[email protected]> 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
