[CALCITE-4989] Nested JSON_OBJECT creation does not produce proper json

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.29.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: json_object pull-request-available
• Assignee: Benchao Li
• Reporter: Mans Singh
• Created: 18/Jan/22 21:15
• Updated: 03/Aug/22 16:21
• Resolved: 29/Mar/22 08:34

Description

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\\\"}\"}"}
-------------------------------------------------------------

 

I was expecting the result as follows (without quote escapes):

 

{"level1":{"level2":{"level3":"val3"}}}
 

Also, see examples created by Stamatis

- https://github.com/zabetak/calcite/commit/988c13ce1ff551d6e4046a3c027ff298f79971f8

Issue Links

• links to: Github PR
• links to: GitHub Pull Request #2730
• links to: GitHub Pull Request #2755