[
https://issues.apache.org/jira/browse/CALCITE-4989?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17497661#comment-17497661
]
Gavin Ray commented on CALCITE-4989:
------------------------------------
Ahh this actually does not appear to fix it in all cases =/
{code:java}
1: jdbc:calcite:model=src/test/resources/exam> SELECT
. . . . . . . . . . . . . . . . . . semicolon> "houses"."id",
. . . . . . . . . . . . . . . . . . semicolon> "houses"."name",
. . . . . . . . . . . . . . . . . . semicolon> JSON_ARRAYAGG(JSON_OBJECT(
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'level1' VALUE 1,
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'level2' VALUE
JSON_OBJECT(
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'level3' VALUE 2
. . . . . . . . . . . . . . . . . . . . . . )> )
. . . . . . . . . . . . . . . . . . . . . . )> ))
. . . . . . . . . . . . . . . . . . semicolon> FROM "houses" AS "houses"
. . . . . . . . . . . . . . . . . . semicolon> GROUP BY "houses"."id",
"houses"."name";
+----+-----------------------------+------------------------------------------------------+
| id | name | EXPR$2
|
+----+-----------------------------+------------------------------------------------------+
| 2 | Small House on Ocean Avenue |
["{\"level1\":\"1\",\"level2\":{\"level3\":\"2\"}}"] |
| 1 | Big House on Main Street |
["{\"level1\":\"1\",\"level2\":{\"level3\":\"2\"}}"] |
+----+-----------------------------+------------------------------------------------------+
{code}
{code:java}
1: jdbc:calcite:model=src/test/resources/exam> SELECT JSON_ARRAYAGG(JSON_OBJECT(
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'id' VALUE "houses"."id",
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'address' VALUE
"houses"."address",
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'users' VALUE (
. . . . . . . . . . . . . . . . . . . . . . )> SELECT
JSON_ARRAYAGG(JSON_OBJECT(
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'id' VALUE
"users"."id",
. . . . . . . . . . . . . . . . . . . . . . )> KEY 'name' VALUE
"users"."name"
. . . . . . . . . . . . . . . . . . . . . . )> ))
. . . . . . . . . . . . . . . . . . . . . . )> FROM "users"
. . . . . . . . . . . . . . . . . . . . . . )> WHERE "users"."house_id"
= "houses"."id"
. . . . . . . . . . . . . . . . . . . . . . )> )
. . . . . . . . . . . . . . . . . . . . . . )> ))
. . . . . . . . . . . . . . . . . . semicolon> FROM "houses" AS "houses";
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ["{\"address\":\"123 Main
Street\",\"id\":\"1\",\"users\":\"[\\\"{\\\\\\\"name\\\\\\\":\\\\\\\"John\\\\\\\",\\\\\\\"id\\\\\\\":\\\\\\\"1\\\\\\\"}\\\",\\\"{\\\\\\\"name\
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{code}
> Nested JSON_OBJECT creation does not produce proper json
> --------------------------------------------------------
>
> Key: CALCITE-4989
> URL: https://issues.apache.org/jira/browse/CALCITE-4989
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.29.0
> Reporter: Mans Singh
> Priority: Critical
> Labels: json_object, pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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 :
>
> {code:java}
> select JSON_OBJECT(
> KEY 'level1'
> VALUE(
> JSON_OBJECT(
> KEY 'level2'
> VALUE(
> JSON_OBJECT(
> KEY 'level3'
> VALUE 'val3')
> )
> )
> )
> )
> from (values ('{"a":{"b":2}}')) t(v);
>
> {code}
> And it produces the result:
>
> {noformat}
> -------------------------------------------------------------
> EXPR$0
> -------------------------------------------------------------
> {"level1":" {\"level2\":\"{\\\"level3\\\":\\\"val3\\\"}\"}"}
> -------------------------------------------------------------
> {noformat}
>
> I was expecting the result as follows (without quote escapes):
>
> {noformat}
> {"level1":{"level2":{"level3":"val3"}}}
>
> {noformat}
> Also, see examples created by Stamatis
> -
> [https://github.com/zabetak/calcite/commit/988c13ce1ff551d6e4046a3c027ff298f79971f8]
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
