时间戳

首页 > 解决方案 > 使用 UNION ALL 的嵌套查询的 SQL JSON 对象返回字符串

sql - 使用 UNION ALL 的嵌套查询的 SQL JSON 对象返回字符串

问题描述

我需要关于使用 UNION ALL 创建 SQL json 嵌套对象的帮助，我有嵌套查询，我想使用 union all 将一些默认对象传递给查询，但目前它返回嵌套查询的字符串对象。

这是我的查询

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

并返回 json 对象如下

[{"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\"}]"}]

对于 ques 嵌套对象，格式错误。预期输出：

[{"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"}]}]

请帮我解决一下这个。

标签： sqljsonsql-server

解决方案

当您使用 时FOR JSON AUTO，JSON文本被转义。您可以尝试JSON_QUERY再次使用来解决此问题。

从文档：

没有可选第二个参数的 JSON_QUERY 只返回第一个参数作为结果。由于 JSON_QUERY 总是返回有效的 JSON，FOR JSON 知道这个结果不必被转义。

    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

推荐阅读