从 JSON SQL 查询中删除反斜杠

Question

我正在使用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}
}

Answer 1

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

FOR JSON子句转义带有 的 JSON 输出中的特殊字符。

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

如果您确定生成了有效的JSON，则可以尝试将JSON_QUERY与 FOR JSON 一起使用。

。JSON_QUERY返回有效的 JSON 片段。因此，对于 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}}]

笔记：

您可以使用ISJSON检查您的JSON：

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