从 JSON SQL 查询中删除反斜杠

我正在使用 SQL Server 查询构造 JSON 对象。我对斜杠有疑问，而且我需要特定格式的数据，如下所述。实际输出中有斜线。我想从输出查询中消除斜杠。

    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

上面的查询给了我想要的输出，但它有反斜杠。

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

实际预期产量低于

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

如果我正确理解您的问题并且您想要删除特殊字符的转义以生成有效的 JSON 内容，则下一个解决方案可能会有所帮助。

FOR JSON clause escapes https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-escapes-special-characters-and-control-characters-sql-server?view=sql-server-2017#escaping-of-special-characters中的特殊字符JSON输出与\.

...如果源数据包含特殊字符，则 FOR JSON 子句在 JSON 输出中使用 \ 对它们进行转义，如 下表。这种转义同时出现在属性名称中 以及他们的价值观。 ...

如果您确定您生成了有效的JSON，你可以尝试使用JSON_QUERY 与 FOR JSON https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-2017#using-json_query-with-for-json.

... JSON_QUERY 返回有效的 JSON 片段。结果，FOR JSON 不转义 JSON_QUERY 返回值中的特殊字符。 ...

简单的例子，说明了你的问题：

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

解决方案：

-- 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:

您可以使用以下命令检查您的 JSONISJSON https://learn.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql?view=sql-server-2017:

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