Parsing full JSON into SQL

Question

I have the following JSON (only a couple of nodes for display purposes):

[
  {
    "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
      }
    ]
  }
]

I am not particularly good at SQL and have been frantically relearning what I did at uni and an old job 13yrs ago, in order to complete a project that takes JSON data from APIs of a care management solution, into my Delphi application that then processes the data to work out this and that. The JSON format is different depending on the report, and in the case of the CareNotesReport, the above JSON is what is produced.

My Delphi app pulls this JSON verbatim and dumps it to a .json file, with an ADO query that then executes the following code (along with some other select queries that are irrelevant here):

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

I have a couple of issues. With the above code, I've had to strip the first [ and ] from the JSON file to get it to work, but due to the size of some returns I've had to change the use of a memorystream to a filestream within Delphi. This has created the problem in that although I can trim the last ] of the file, I am so far unable to find a reliable (and easy) method for trimming the first [. Therefore, I'm forced to conclude that it is my SQL code that is the weak link, and that it needs to be able to process the JSON with these two characters included.

Please can someone show me where I'm going wrong. I'm aware that the original JSON appears to be array, object, array, object, but I don't know where to go from that. Also, I've fudged the ability to read SliderIcons as I know there are a maximum of 5 objects of that array, but would prefer a more dynamic solution if possible.

Any assistance given in terms of either the way to remove that first [ in Delphi, or better SQL to handle the original JSON would be greatly appreciated.

Regards Ant

Parsing full JSON into SQL

Answers (1)

Related Questions