时间戳

首页 > 解决方案 > 将完整的 JSON 解析为 SQL

sql - 将完整的 JSON 解析为 SQL

问题描述

我有以下 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 年前在大学所做的工作和一份旧工作，以完成一个项目，该项目将来自护理管理解决方案的 API 的 JSON 数据导入我的 Delphi 应用程序，然后处理数据来解决这个和那个。JSON 格式因报告而异，在 CareNotesReport 的情况下，生成的是上述 JSON。

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

use CMUtility;


DECLARE @JSON VARCHAR(MAX)

SELECT @JSON = BulkColumn
FROM OPENROWSET 
(BULK 'C:\Users\User\Documents\Embarcadero\Studio\Projects\CMU\Win32\Debug\carenotesreport.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 中删除第一个 [ 的方式或更好的 SQL 来处理原始 JSON 的任何帮助都将不胜感激。

问候蚂蚁

标签： sqljsondelphi

解决方案

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

JSON：

DECLARE @json nvarchar(max)

--SELECT @json = BulkColumn
--FROM OPENROWSET (BULK 'C:\Users\User\Documents\Embarcadero\Studio\Projects\CMU\Win32\Debug\carenotesreport.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_QUERYJSON从字符串中提取对象或数组。如果值不是对象或数组，则结果为模式NULL，lax模式为错误strict。
  • 函数JSON_VALUEJSON从字符串中提取标量值。如果path指向的不是标量值，则结果为NULL模式且模式lax错误strict

注释（Delphi和SQL Server）：

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

推荐阅读