JSON Query return result with escaped character

Question

From the following query I am getting result as :

"Units": "[{\"Name\":\"Unit25\"}]",

Query is as follow query which reproducing this result, I have simplified the query actual query is used but based selection is this:

SELECT (      
SELECT *
  FROM (      
        SELECT
           JSON_QUERY(COALESCE((SELECT [Name] FROM (
                    SELECT [Name] 
                    FROM [TableA]                                                       
                    UNION 

                    SELECT TOP 3 [Name] 
                    FROM [TableB]) [T]
                    FOR JSON PATH),'[]')) AS [Units]
            FROM [User] [U]

               UNION

      SELECT
        JSON_QUERY(COALESCE((SELECT [Name] FROM (
                    SELECT [Name] 
                    FROM [TableA]                                                       
                    UNION 

                    SELECT TOP 3 [Name] 
                    FROM [TableB]) [T]
                    FOR JSON PATH),'[]')) AS [Units]
        FROM [Contact] [C]
         ) [Data] 
    FOR JSON PATH, INCLUDE_NULL_VALUES)

Expected result :

"Units": "[{"Name":"Unit25"}]",

How to achieve ?

Try replacing \" with ". However I wouldn't recommend it. Your application would break serializing/deserializing it. — Krishna, Mar 16 '18 at 16:16

score 0 · Answer 1 · answered Sep 11 '18 at 13:03

The problem here is that using JSON_QUERY before the UNION statement somehow breaks json escaping. Probably you can fix your query using JSON_QUERY as the outmost statement:

select JSON_QUERY(units) as Units from
(
    SELECT
        COALESCE((SELECT [Name] FROM (
                SELECT [Name] 
                FROM [TableA]                                                       
                UNION 

                SELECT TOP 3 [Name] 
                FROM [TableB]) [T]
                FOR JSON PATH),'[]')  AS [Units]
        FROM [User] [U]
           UNION
  SELECT
     COALESCE((SELECT [Name] FROM (
                SELECT [Name] 
                FROM [TableA]                                                       
                UNION 

                SELECT TOP 3 [Name] 
                FROM [TableB]) [T]
                FOR JSON PATH),'[]')  AS [Units]
    FROM [Contact] [C]
) u
FOR JSON auto, INCLUDE_NULL_VALUES

Results:

JSON Query return result with escaped character

1 Answers1