从JSON SQL查询中删除反斜杠 [英] Remove backslashes from JSON SQL Query

问题描述

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

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

实际预期输出低于

"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内容，则下一个解决方案可能会有所帮助.

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子句

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

...如果源数据包含特殊字符，则为FOR JSON 子句使用\在JSON输出中对其进行转义，如 下表.这两个属性名称中都发生这种转义 和他们的价值观. ...

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

如果确定生成有效的JSON，则可以尝试使用

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

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

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

解决方案:

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

注意:

您可以使用 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

这篇关于从JSON SQL查询中删除反斜杠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！