CODEWITHSUNDEEP
sqljsonsql-server
Lifewithsun
Lifewithsun

Reputation: 998

SQL JSON object return string for nested query with UNION ALL

I need help on SQL json nested object creation with UNION ALL, I have nested query and i want to pass some default objects to the query with using union all, but currently it returns string object for nested query.

Here is my query 

Select * from (
SELECT 'Id'=      ent.categoryid , 
       'Text'=    ent.catname , 
       ques = json_query( 
       ( 
              SELECT * 
              FROM   ( 
                            SELECT 'Id'=   q.qid, 
                                   'Text'= q.questext
                            FROM   chatfaqquestionnaire q 
                            WHERE  q.categoryid = ent.categoryid 
                            UNION ALL 
                            SELECT top 1 
                            'Id'= 100000, 
                            'Text'='Talk to Agent'                                   
                            From   [mChatCategory] ent1 where ent1.CategoryId=ent.CategoryId 
                             ) AS t FOR json auto ) )
FROM   [mChatCategory] ent 
Union All
SELECT  top 1
        'Id'=100000 , 
        'Text'='Talk to Agent',
         ques=null      
              from  [mChatCategory] ent 
) AS L1  FOR json auto

and return json object as below

[{"Id":1,"Text":"Food Safety","ques":"[{\"Id\":100000,\"Text\":\"Talk to Agent\"}]"},{"Id":2,"Text":"Permit Releted","ques":"[{\"Id\":1,\"Text\":\"Permit not renewed\\r\\n\"},{\"Id\":2,\"Text\":\"Payment issue\"},{\"Id\":100000,\"Text\":\"Talk to Agent\"}]"}]

which is in wrong format for ques nested object. Expected output:

[{"Id":1,"Text":"Food Safety","ques":[{"Id":100000,"Text":"Talk to Agent"}]},{"Id":2,"Text":"Permit Releted","ques":[{"Id":1,"Text":"Permit not renewed\r\n"},{"Id":2,"Text":"Payment issue"},{"Id":100000,"Text":"Talk to Agent"}]}]

Please help me with this.

Upvotes: 0

Views: 1411

Answers (1)

Zhorov
Zhorov

Reputation: 29943

When you use FOR JSON AUTO, JSON text is escaped. You may try to solve this issue using JSON_QUERY again.

From documentation:

JSON_QUERY without its optional second parameter returns only the first argument as a result. Since JSON_QUERY always returns valid JSON, FOR JSON knows that this result does not have to be escaped.

    Select Id, Text, JSON_QUERY(ques) AS ques 
    from (
    SELECT 'Id'=      ent.categoryid , 
        'Text'=    ent.catname , 
        ques = json_query( 
        ( 
                SELECT * 
                FROM   ( 
                                SELECT 'Id'=   q.qid, 
                                    'Text'= q.questext
                                FROM   chatfaqquestionnaire q 
                                WHERE  q.categoryid = ent.categoryid 
                                UNION ALL 
                                SELECT top 1 
                                'Id'= 100000, 
                                'Text'='Talk to Agent'                                   
                                From   [mChatCategory] ent1 where ent1.CategoryId=ent.CategoryId 
                                ) AS t FOR json auto ) )
    FROM   [mChatCategory] ent 
    Union All
    SELECT  top 1
            'Id'=100000 , 
            'Text'='Talk to Agent',
            ques=null      
                from  [mChatCategory] ent 
    ) AS L1  FOR json auto

Upvotes: 3

Related Questions