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