SQL JSON object return string for nested query with UNION ALL

Question

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.

You mean that SELECT top 1 'Id'=100000 , 'Text'='Talk to Agent',ques=null from [mChatCategory] ent makes JSON output not as you expect? — Zhorov
– Zhorov, Commented Jun 12, 2019 at 9:07

Zhorov · Accepted Answer · 2019-06-12 20:31:11Z

3

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

edited Jun 12, 2019 at 20:31

answered Jun 12, 2019 at 9:18

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lifewithsun Over a year ago

It returns me error with Msg 8155, Level 16, State 2, Line 31 No column name was specified for column 1 of 'L2'.

Zhorov Over a year ago

@Lifewithsun Can you check again, I've made an update. I've copied wrong code from my reproduction test statement.

Collectives™ on Stack Overflow

SQL JSON object return string for nested query with UNION ALL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related