Remove backslashes from JSON SQL Query

Question

I am constructing JSON object using SQL Server query. I have issues with slashes and also i need data in specific format as mentioned below. The actual output has slashes in it. I want to eliminate slashes from my output query.

    SELECT (SELECT 
         ('{'+'"value":'+ cast(ISNULL(SP.ACCOUNT_TRAFFIC_DEP_LICENSE_NUMBER,'') as varchar) )                       AS TRAFFIC_DEP_LICENSE_NUMBER
        ,(  '{' +'"value":'+cast(ISNULL(SP.NUMBER_OF_VEHICLES   ,'')    as varchar)     )                                   AS NUMBER_OF_VEHICLES
         ,( '{' +'"value":'+cast(ISNULL(SP.ACCOUNT_TRAFFIC_DEP_LICENSE_START_H_DATE,'') as varchar) )                                       AS TRAFFIC_DEP_LICENSE_START_H_DATE
         ,( '{' +'"value":'+cast(ISNULL(SP.ACCOUNT_TRAFFIC_DEP_LICENSE_END_H_DATE   ,'')    as varchar)     )                           AS TRAFFIC_DEP_LICENSE_END_H_DATE
        ,(  '{' +'"value":'+cast(ISNULL(SP.SUSPENSION_BY_MORROR_STATUS,'')  as varchar)         )                                           AS SUSPENSION_BY_MORROR_STATUS
         ,( '{' +'"value":'+cast(ISNULL(SP.SUSPENSION_BY_VIOLATION  ,'')    as varchar)     )                                           AS SUSPENSION_BY_VIOLATION
         ,( '{' +'"value":'+cast(ISNULL(SP.SUSPENSION_BY_PENALTY        ,'')    as varchar)     )                                           AS SUSPENSION_BY_PENALTY
                from [dbo].[TAMM_CAC_ACCOUNTS_STG]  SP 
                where SP.Account_Reference_Number = CAC.Account_Reference_Number
                AND (
                ACCOUNT_TRAFFIC_DEP_LICENSE_NUMBER IS NOT NULL OR 
                NUMBER_OF_VEHICLES IS NOT NULL OR
                SP.ACCOUNT_TRAFFIC_DEP_LICENSE_END_H_DATE   IS NOT NULL OR
                SP.ACCOUNT_TRAFFIC_DEP_LICENSE_END_H_DATE   IS NOT NULL OR
                SP.SUSPENSION_BY_MORROR_STATUS              IS NOT NULL OR
                SP.SUSPENSION_BY_VIOLATION          IS NOT NULL OR      
                SP.SUSPENSION_BY_PENALTY        IS NOT NULL     )   FOR JSON PATH,INCLUDE_NULL_VALUES


 ) AS specifics
            from dbo.Customer_Account CAC

The above query gives me desired output but It has backslashes.

[
  {
    "TRAFFIC_DEP_LICENSE_NUMBER": "{\"value\":0",
    "NUMBER_OF_VEHICLES": "{\"value\":1009",
    "TRAFFIC_DEP_LICENSE_START_H_DATE": "{\"value\":14351229",
    "TRAFFIC_DEP_LICENSE_END_H_DATE": "{\"value\":14351229",
    "SUSPENSION_BY_MORROR_STATUS": "{\"value\":0",
    "SUSPENSION_BY_VIOLATION": "{\"value\":1",
    "SUSPENSION_BY_PENALTY": "{\"value\":1"
  }
]

actual expected output is below

“specific”:{
"TRAFFIC_DEP_LICENSE_NUMBER":{“value”:0},
            "NUMBER_OF_VEHICLES":{“value”:22},
            "TRAFFIC_DEP_LICENSE_START_H_DATE":{“value”: 14480608},
            "TRAFFIC_DEP_LICENSE_END_H_DATE":{“value”: 14480608},
            "SUSPENSION_BY_MORROR_STATUS":{“value”:0},
            "SUSPENSION_BY_VIOLATION":{“value”:1},
            "SUSPENSION_BY_PENALTY":{“value”:1},
}

Answer 1

If I understand your question correctly and you want to remove escaping of the special characters to generate a valid JSON content, next solution may help.

FOR JSON clause escapes special characters in the JSON output with \.

... If the source data contains special characters, the FOR JSON clause escapes them in the JSON output with \, as shown in the following table. This escaping occurs both in the names of properties and in their values. ...

If you are sure, that you generate a valid JSON, you may try to use JSON_QUERY with FOR JSON.

... JSON_QUERY returns a valid JSON fragment. As a result, FOR JSON doesn't escape special characters in the JSON_QUERY return value. ...

Simple example, that demonstrates your issue:

-- Statement:
SELECT (
   SELECT '{"value": 1}' AS TRAFFIC_DEP_LICENSE_NUMBER
   FOR JSON PATH, INCLUDE_NULL_VALUES
) AS specifics

-- Output:
-------------------------------------------------
specifics
-------------------------------------------------
[{"TRAFFIC_DEP_LICENSE_NUMBER":"{\"value\": 1}"}]

Solution:

-- Statement:
SELECT (
   SELECT JSON_QUERY('{"value": 1}') AS TRAFFIC_DEP_LICENSE_NUMBER
   FOR JSON PATH, INCLUDE_NULL_VALUES
) AS specifics

-- Output:
-------------------------------------------------
specifics
-------------------------------------------------
[{"TRAFFIC_DEP_LICENSE_NUMBER":{"value": 1}}]

Notes:

You may check your JSON using ISJSON:

SELECT (
   SELECT CASE
      WHEN ISJSON('{"value": 1}') = 1 THEN JSON_QUERY('{"value": 1}') 
      ELSE 'Invalid JSON'
   END AS TRAFFIC_DEP_LICENSE_NUMBER
   FOR JSON PATH, INCLUDE_NULL_VALUES
) AS specifics