You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by M Singh <ma...@yahoo.com.INVALID> on 2022/01/17 14:40:38 UTC
Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
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
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by M Singh <ma...@yahoo.com.INVALID>.
Thanks Hongze for your recommendations.
On Saturday, January 22, 2022, 09:59:00 PM EST, Hongze Zhang <no...@126.com> wrote:
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 <ma...@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 <za...@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 <ma...@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
>
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by Hongze Zhang <no...@126.com>.
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 <ma...@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 <za...@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 <ma...@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
>
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by Stamatis Zampetakis <za...@gmail.com>.
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 <ma...@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 <
> mans2singh@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 <
> zabetak@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 <ma...@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
>
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by M Singh <ma...@yahoo.com.INVALID>.
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 <ma...@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 <za...@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 <ma...@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
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by M Singh <ma...@yahoo.com.INVALID>.
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 <za...@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 <ma...@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
Re: Apache Calcite - How to create proper nested json object using JSON_OBJECT (it produces json with escapes)
Posted by Stamatis Zampetakis <za...@gmail.com>.
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 <ma...@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