时间戳

首页 > 解决方案 > SQL Server - OPENJSON 错误 - JSON 文本格式不正确

json - SQL Server - OPENJSON 错误 - JSON 文本格式不正确

问题描述

Azure SQL Server 2017 -

我们有一个表，其中包含dbo.MailArchive一个名为的字段Mail_Body，其中包含电子邮件的正文。从记录到记录，数据总是这样，只是数字不同，状态消息不同：

Status: Completed
Successful actions count: 250
Page load count: 250

但是复制/粘贴上面的内容进行测试会使它看起来没有问题。您可以使用它来复制问题：

DECLARE @YourString varchar(8000) = 'Status: Completed
Successful actions count: 250
Page load count: 250' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);


SELECT CONCAT('{"', REPLACE(REPLACE(@YourString, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')

此外，如果我在 Word 中查看并打开隐藏字符，这就是电子邮件正文的样子：

电子邮件正文图片

这是数据导出到数据库的格式。

我正在尝试OPENJSON通过换行符来解析这些数据，例如：

SELECT Mail_Body,
j.*
FROM dbo.MailArchive d
CROSS APPLY OPENJSON (CONCAT('{"', REPLACE(REPLACE(d.Mail_Body, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')) 
WITH (
   Status varchar(100) '$.Status',
   Successful_Actions_Count int '$."Successfull actions count"',
   Request_Count int '$."Request count"'
     ) j

执行此操作时出现以下错误：

JSON 文本格式不正确。在位置 246 处发现意外字符“,”。

根据我迄今为止收到的一些建议，我认为这可能与正文末端的换行符有关。但我无法找出正确的语法来解释它。

标签： jsonsql-server

解决方案

这是一个昂贵的修复，因为REVERSE它并不便宜，但您可以使用它并PATINDEX找到不是换行符或回车符的第一个字符，删除它们，然后解析：

DECLARE @YourString varchar(8000) = 'Status: Completed
Successful actions count: 250
Page load count: 250' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);

DECLARE @PI varchar(7) = '%[^' + CHAR(13) + CHAR(10) + ']%';

SELECT j.Status,
       j.Successful_Actions_Count, --NULL as not in sample data
       j.Request_Count --NULL as not in sample data
FROM (VALUES(@YourString))V(YS)
     CROSS APPLY(VALUES(REVERSE(V.YS),PATINDEX(@PI,REVERSE(V.YS)))) PI(SY,I)
     CROSS APPLY(VALUES(REVERSE(STUFF(PI.SY,1,PI.I,''))))S(FixedString)
     CROSS APPLY OPENJSON (CONCAT('{"', REPLACE(REPLACE(S.FixedString, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')) 
                 WITH (Status varchar(100) '$.Status',
                       Successful_Actions_Count int '$."Successfull actions count"',
                       Request_Count int '$."Request count"') j;

这假设CHAR(13) + CHAR(10)在字符串的末尾可能有 0 到多个集合。如果它只有 2 套，简单地使用SUBSTRING会LEN更容易。

推荐阅读