server sql 去 反斜杠,从JSON SQL查询中删除反斜杠

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},

}

解决方案

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