正在将完整的JSON解析为SQL

Question

我有以下JSON(只有几个节点用于显示(：

[
{
"CareNotes": [
{
"CareNoteID": "34289e11-6433-4020-9734-224eb8caa11a",
"CareNoteExtendedID": "00000000-0000-0000-0000-000000000000",
"ADLName": "Mobility",
"FlagsText": "",
"Note": "Help with walking, used as four wheel walker, was content.",
"AnswerType": 1,
"Fragment": "Help with walking",
"RemedialText": null,
"Details": null,
"ServiceUserID": "bc300962-3653-491a-9ba9-afab10964af4",
"ServiceUser": "Betty Test",
"ServiceUserLastName": "Test",
"ServiceUserForeNames": "Betty",
"ServiceUserDateofBirth": "19/03/1901",
"ServiceUserLocation": 15,
"WorkerID": "53e6c7b9-2c80-451e-ba8c-abfb309380ac",
"Worker": "Beth Beth",
"VoidedByWorker": null,
"_supersedeStackID": null,
"SupersededByWorker": null,
"WorkerLastName": "Beth",
"DisplayOnShiftHandover": 0,
"WorkerInitials": "B.B.",
"SliderData": "Walk",
"SliderData2": "Not entered",
"SliderIcons": [
{
"IconID": 1093,
"CareNoteText": "was content"
},
{
"IconID": 1156,
"CareNoteText": "used as four wheel walker"
}
],
"DateDone": "2019-09-30T21:24:41.994+00:00",
"DateDoneSU": "2019-09-30T21:24:41.994+00:00",
"Duration": "9 minutes",
"DurationInt": 9,
"ActionIconID": 6001,
"mraCareOrder": 5000,
"wasPlanned": false,
"qrVerified": false,
"qrData": null,
"nfcVerified": null,
"inVerified": null,
"ViaMonitor": null
}
]
}
]

我不是特别擅长SQL，我一直在疯狂地重新学习我在大学和13年前的一份老工作所做的事情，以便完成一个项目，将JSON数据从护理管理解决方案的API中提取到我的Delphi应用程序中，然后处理数据来计算这个和那个。JSON格式因报告而异，在CareNotesReport的情况下，生成的是上面的JSON。

我的Delphi应用程序逐字逐句地提取这个JSON，并将其转储到.JSON文件中，其中包含一个ADO查询，然后执行以下代码(以及其他一些与此处无关的选择查询(：

use CMUtility;

DECLARE @JSON VARCHAR(MAX)
SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:UsersUserDocumentsEmbarcaderoStudioProjectsCMUWin32Debugcarenotesreport.json', SINGLE_CLOB) 
AS j
drop table if exists jsoncarenotes
select * into JSONCareNotes
from OPENJSON(@JSON,'$.CareNotes')
with (
DateDone nvarchar(10) '$.DateDone',
ServiceUser nvarchar(100) '$.ServiceUser',
ServiceUserLastName nvarchar(50) '$.ServiceUserLastName',
SUDOB nvarchar(15) '$.ServiceUserDateofBirth',
Note nvarchar(255) '$.Note',
ADLName nvarchar(200) '$.ADLName',
FlagsText nvarchar(255) '$.FlagsText',
Fragment nvarchar(255) '$.Fragment',
RemedialText nvarchar(255) '$.RemedialText',
Worker nvarchar(30) '$.Worker',
ServiceUserID nvarchar(100) '$.ServiceUserID',
WorkerID nvarchar(100) '$.WorkerID',
CareNoteID nvarchar(255) '$.CareNoteID',
SID1 nvarchar(255) '$.SliderIcons[0].IconID',
SText1 nvarchar(255) '$.SliderIcons[0].CareNoteText',
SID2 nvarchar(255) '$.SliderIcons[1].IconID',
SText2 nvarchar(255) '$.SliderIcons[1].CareNoteText',
SID3 nvarchar(255) '$.SliderIcons[2].IconID',
SText3 nvarchar(255) '$.SliderIcons[2].CareNoteText',
SID4 nvarchar(255) '$.SliderIcons[3].IconID',
SText4 nvarchar(255) '$.SliderIcons[3].CareNoteText',
SID5 nvarchar(255) '$.SliderIcons[4].IconID',
SText5 nvarchar(255) '$.SliderIcons[4].CareNoteText'
)
as CareNotes

我有几个问题。在上面的代码中，我不得不从JSON文件中去掉第一个[和]以使其工作，但由于一些返回的大小，我不得不在Delphi中将内存流的使用改为文件流。这就造成了一个问题，尽管我可以修剪文件的最后一个]，但到目前为止，我还无法找到一个可靠(简单(的方法来修剪第一个[。因此，我不得不得出结论，是我的SQL代码是薄弱环节，它需要能够处理包含这两个字符的JSON。

有人能告诉我哪里出了问题吗。我知道最初的JSON看起来是数组、对象、数组、对象，但我不知道该怎么做。此外，我还伪造了读取SliderIcons的能力，因为我知道该数组最多有5个对象，但如果可能的话，我更喜欢更动态的解决方案。

无论是在Delphi中删除第一个JSON的方法，还是更好的SQL来处理原始JSON，我们都将不胜感激

问候Ant

Answer 1

您可以使用SQL Server功能解析此JSON输入。如果您的JSON输入具有此固定格式(一个包含一个项和嵌套JSON数组的数组(，则需要使用带有OPENJSON()调用的额外APPLY运算符来解析嵌套JSON数组。请注意，当引用的属性包含内部JSON对象或数组时，需要在列定义中使用AS JSON选项。

JSON:

DECLARE @json nvarchar(max)
--SELECT @json = BulkColumn
--FROM OPENROWSET (BULK 'C:UsersUserDocumentsEmbarcaderoStudioProjectsCMUWin32Debugcarenotesreport.json', SINGLE_CLOB) AS j
SELECT @json = N'[
{
"CareNotes":[
{
"CareNoteID":"34289e11-6433-4020-9734-224eb8caa11a",
"CareNoteExtendedID":"00000000-0000-0000-0000-000000000000",
"ADLName":"Mobility",
"FlagsText":"",
"Note":"Help with walking, used as four wheel walker, was content.",
"AnswerType":1,
"Fragment":"Help with walking",
"RemedialText":null,
"Details":null,
"ServiceUserID":"bc300962-3653-491a-9ba9-afab10964af4",
"ServiceUser":"Betty Test",
"ServiceUserLastName":"Test",
"ServiceUserForeNames":"Betty",
"ServiceUserDateofBirth":"19/03/1901",
"ServiceUserLocation":15,
"WorkerID":"53e6c7b9-2c80-451e-ba8c-abfb309380ac",
"Worker":"Beth Beth",
"VoidedByWorker":null,
"_supersedeStackID":null,
"SupersededByWorker":null,
"WorkerLastName":"Beth",
"DisplayOnShiftHandover":0,
"WorkerInitials":"B.B.",
"SliderData":"Walk",
"SliderData2":"Not entered",
"SliderIcons":[
{
"IconID":1093,
"CareNoteText":"was content"
},
{
"IconID":1156,
"CareNoteText":"used as four wheel walker"
}
],
"DateDone":"2019-09-30T21:24:41.994+00:00",
"DateDoneSU":"2019-09-30T21:24:41.994+00:00",
"Duration":"9 minutes",
"DurationInt":9,
"ActionIconID":6001,
"mraCareOrder":5000,
"wasPlanned":false,
"qrVerified":false,
"qrData":null,
"nfcVerified":null,
"inVerified":null,
"ViaMonitor":null
}
]
}
]'

声明：

SELECT 
j1.DateDone,
j1.Note,
j2.IconID,
j2.CareNoteText
--INTO JSONCareNotes    
FROM OPENJSON(@json, '$[0].CareNotes') WITH (
DateDone nvarchar(10) '$.DateDone',
Note nvarchar(255) '$.Note',
-- add additional columns definitons
SliderIcons nvarchar(max) AS JSON
) j1
CROSS APPLY OPENJSON(j1.SliderIcons) WITH (
IconID int '$.IconID',
CareNoteText nvarchar(100) '$.CareNoteText'
) j2

结果：

DateDone    Note                                                        IconID  CareNoteText
2019-09-30  Help with walking, used as four wheel walker, was content.  1093    was content
2019-09-30  Help with walking, used as four wheel walker, was content.  1156    used as four wheel walker

备注(JSON基础(：

• 当您想解析JSON字符串并将结果作为表时，请使用OPENJSON表值函数，使用默认或显式模式
• 函数JSON_QUERY从JSON字符串中提取对象或数组。如果该值不是对象或数组，则结果在lax模式下为NULL，在strict模式下为错误
• 函数JSON_VALUE从JSON字符串中提取标量值。如果path指向的不是标量值，则结果为lax模式中的NULL和strict模式中的错误

注释(Delphi和SQL Server(：

• 您可以将逻辑组织为一个存储过程，该过程有一个参数-JSON文本。在这种情况下，您将直接将JSON发送到SQL Server，并且不需要使用OPENROWSET()(OPENJSON()需要额外的权限(
• 使用Delphi执行存储过程是一项简单的任务，例如使用ADO