SQL Server 2016转义字符问题

Question

因此，我正在使用'FOR JSON'从SQL Server 2016生成JSON文件。

我使用JSON_QUERY包装查询，以防止在生成的双引号（"）之前出现转义字符。这部分工作得很好，但是格式化日期中的斜杠（/）仍然会显示。

需要注意的一点是，我正在使用以下方法将SQL中的datetime对象转换为CONVERT(VARCHAR，[dateEntity]，101)。

以下是一个示例（这是子查询）

JSON_QUERY((
SELECT [LegacyContactID]
      ,[NameType]
      ,[LastName]
      ,[FirstName]
      ,[Active]
      ,[Primary]
      ,CONVERT(VARCHAR,[StartDate],101) AS [StartDate]
      ,CONVERT(VARCHAR,[EndDate],101) AS [EndDate]
FROM [LTSS].[ConsumerFile_02_ContactName]
WHERE [LegacyContactID] = ContactList.[LegacyContactID]
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)) AS ContactName

结果将会是

"ContactName": {
    "LegacyContactID": "123456789",
    "NameType": "Name",
    "LastName": "Jack",
    "FirstName": "Apple",
    "Active": true,
    "Primary": true,
    "StartDate": "04\/01\/2016",
    "EndDate": "04\/30\/2016"
}

我将整个查询都包装在JSON_QUERY中以消除转义，但仍然在日期的斜杠上进行了转义。我尝试将日期作为字符串传递而没有进行转换，结果仍然相同。有什么见解吗？

- Chris

Answer 1

一个解决方法是通常避免在日期中使用“/”，而是使用“正确”的JSON数据格式

SELECT JSON_QUERY((
    SELECT TOP 1 object_id, create_date
    FROM sys.tables
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
))

结果

{"object_id":18099105,"create_date":"2017-08-14T11:19:22.670"}

- Martin Thøgersen

Answer 2

我遇到了一些类似的问题。不想详细说明，但我认为这是新的JSON功能尚未被广泛采用的原因之一。

我在MSDN上添加了一些评论和推文：

"为什么不能通过标志关闭所有字符串的自动转义？" - https://msdn.microsoft.com/en-us/library/dn921889.aspx

"接近成功，但还没有..." - https://msdn.microsoft.com/en-us/library/dn921882.aspx

"有没有其他人对@SQLServer / @AzureSQLDB中所有JSON强制自动转义感到沮丧？（请参见我的评论链接）msdn.microso…" - https://twitter.com/brian_jorden/status/844621512711831552

如果您找到了处理此问题的方法或方式，希望能在这些线程中听到，并祝好运...

- Brian Jorden

---

问题出在第三方需要从我这里获取数据。我会发送包含转义字符的数据，但他们的文件上传失败了。他们把问题归咎于转义字符，但事实证明这与转义字符无关，而是他们对文件的解析不正确。所以我不需要担心删除转义字符。 - Chris

这个链接有帮助吗？[https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server#prevent-double-escaped-json-in-for-json-output] - Fawad Raza

@Fwd079 其实这种方法更接近了，尽管我认为这是一个非常错综复杂的处理方式，因为它已经在他们的“解决常见问题”部分中进行了识别。我一直在使用超级hacky的解决方法，即将我的json结果包装在replace(some_json,'\/','/')中。我已经更新了现在替换文档页面上的评论，因为在他们将事情移动到此处之后，我的旧评论已经消失了：https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-escapes-special-characters-and-control-characters-sql-server - Brian Jorden

Answer 3

更新：

啊，是的，转义和CLRF字符。

除非您的环境显示有问题的字符，否则您将被迫手动从结果集中复制并粘贴，并从那里替换字符串。

现在，您最近更新中提到的内容让我考虑为什么您需要首先转换数据。默认情况下，DATES没有格式化，因此，除非JSON与处理SQL日期不兼容，否则如果您的目标表强制执行正确的格式，则无需在JSON内转换此数据。

因此，除非仍然存在数据截断的问题，在ETL方面，您可以通过两种方式完成此操作：

1-使用临时表

• 暂存表可以是临时表、CTE或实际的空表，您可以使用它们来提取、清洗和转换数据。
• 优点：您只需处理正在插入的行，无需担心约束，并且可以轻松修改JSON之外的任何损坏或非结构化方面的数据。
• 缺点：暂存表可能在数据库中表示更多对象，具体取决于需要的重复程度。因此，找到更好的、一致的结构化数据是更可取的。

2-更改表以使用字符串

• 在这里，您可以在插入持久表后执行业务规则清理数据。
• 优点：您可以节省空间，简化清理过程，并仍然可以使用索引。 SQL Server 在解析 DATE 字符串方面非常高效，仍然可以利用 EXISTS() 和可能的 SARGS 在运行插入时检查非日期。
• 缺点：将日期存储为字符串后，您将失去对表的主要完整性检查，从而打开了暴露脏数据的可能性。您的 UPDATE 语句将被强制使用整个表，这可能会拖慢性能。

    JSON_QUERY((
SELECT [LegacyContactID]
      ,[NameType]
      ,[LastName]
      ,[FirstName]
      ,[Active]
      ,[Primary]
      ,[StartDate] --it already is in a dateformat
      ,[EndDate]
FROM [LTSS].[ConsumerFile_02_ContactName]
WHERE [LegacyContactID] = ContactList.[LegacyContactID]
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)) AS ContactName

- clifton_h

---

这不起作用是因为 CONVERT 函数正在生成格式为 'MM/DD/YYYY' 的日期，然后 FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER 添加了斜杠的转义序列。我尝试在选择语句周围应用 JSON_QUERY，但它对日期没有帮助。 - Chris