Unable to parse string using OPENJSON

Question

I haven't much experience with OPENJSON and I have tried to use the examples but I have been unable to parse the string below

Would anybody be able to help me.

I would like it to be in the format:

Date,TimeSheet.StartTime,TimeSheet.FinishTime,Timesheet.BreakStart,TimeSheet.BreakFinish,Timesheet.EmployeeId,TimeSheet.Notes

[
{
    "Date": "0001-01-01T00:00:00+00:00",
    "TimeSheet": [
        {
            "StartTime": "0001-01-01T00:00:00+00:00",
            "FinishTime": "0001-01-01T00:00:00+00:00",``
            "BreakTimes": [
                "0001-01-01T00:00:00+00:00",
                "0001-01-01T00:00:00+00:00"
            ],
            "EmployeeId": 1,
            "Notes": "Blah, blah, blah..."
        }
    ]
},
{
    "Date": "0001-01-01T00:00:00+00:00",
    "TimeSheet": [
        {
            "StartTime": "0001-01-01T00:00:00+00:00",
            "FinishTime": "0001-01-01T00:00:00+00:00",
            "BreakTimes": [
                "0001-01-01T00:00:00+00:00",
                "0001-01-01T00:00:00+00:00"
            ],
            "EmployeeId": 1,
            "Notes": "Blah, blah, blah..."
        }
    ]
}    
]

Here is the code I am trying to use:

DECLARE @json NVARCHAR(MAX)
DECLARE @json2 NVARCHAR(MAX)

SET @json= (Select [TimeSheetTestData] FROM [TimeSheetTestData] WHERE [TimeSheetTestDataId] = 3) -- this is the sample json above

SET @json2=(SELECT Value
FROM OpenJson(@json) where [Key] = 'TimeSheet')

--SELECT [Key], Value
--FROM OpenJson(@json2)

SELECT * FROM  
OPENJSON ( @json2 )  
WITH (   
              startTime   varchar(200) '$.StartTime' ,
              finishTime   varchar(200) '$.FinishTime' ,
              breakTimes   varchar(200) '$.BreakTimes' ,
              employeeId   varchar(200) '$.EmployeeId' ,
              notes   varchar(200) '$.Notes' 
 )

please specify the error or your actual problem with using OPENJSON. — parisssss, Jun 03 '19 at 06:57
Without sample data and your attempts, this is impossible nfor us to help you with I'm afraid. We have you expected results, but that's it. Show us what you tried, and explain why it didn't work. — Thom A, Jun 03 '19 at 06:59
Sorry - I am trying the following code (the json I posted above is the data from the — MichaelIre, Jun 03 '19 at 07:00

Zhorov · Accepted Answer · 2019-06-03T11:41:35.607

What you need to do is to use OPENJSON() with explicit schema and CROSS APPLY operator.

Input:

DECLARE @json nvarchar(max) = N'
[
{
    "Date": "0001-01-01T00:00:00+00:00",
    "TimeSheet": [
        {
            "StartTime": "0001-01-01T00:00:00+00:00",
            "FinishTime": "0001-01-01T00:00:00+00:00",
            "BreakTimes": [
                "0001-01-01T00:00:00+00:00",
                "0001-01-01T00:00:00+00:00"
            ],
            "EmployeeId": 1,
            "Notes": "Blah, blah, blah..."
        }
    ]
},
{
    "Date": "0001-01-01T00:00:00+00:00",
    "TimeSheet": [
        {
            "StartTime": "0001-01-01T00:00:00+00:00",
            "FinishTime": "0001-01-01T00:00:00+00:00",
            "BreakTimes": [
                "0001-01-01T00:00:00+00:00",
                "0001-01-01T00:00:00+00:00"
            ],
            "EmployeeId": 1,
            "Notes": "Blah, blah, blah..."
        }
    ]
}    
]'

T-SQL:

SELECT 
   j1.[Date], j2.StartTime, j2.FinishTime, j2.BreakStart, j2.BreakFinish, j2.EmployeeId, j2.Notes
FROM OPENJSON(@json)
WITH (
   [Date] nvarchar(25) '$.Date',
   TimeSheet nvarchar(max) '$.TimeSheet' AS JSON
) j1
CROSS APPLY OPENJSON(j1.TimeSheet) 
WITH (
   StartTime nvarchar(25) '$.StartTime',
   FinishTime nvarchar(25) '$.FinishTime',
   BreakStart nvarchar(25) '$.BreakTimes[0]',
   BreakFinish nvarchar(25) '$.BreakTimes[1]',
   EmployeeId int '$.EmployeeId',
   Notes nvarchar(max) '$.Notes'
) j2

Output:

Date                        StartTime                   FinishTime                  BreakStart                  BreakFinish                 EmployeeId  Notes
0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   1           Blah, blah, blah...
0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   0001-01-01T00:00:00+00:00   1           Blah, blah, blah...

Update:

If your JSON is always with one item in "TimeSheet" JSON array, next statement is also an option:

SELECT 
   j.[Date], 
   j.StartTime,
   j.FinishTime,
   j.BreakStart,
   j.BreakFinish,
   j.EmployeeId,
   j.Notes
FROM OPENJSON(@json)
WITH (
   [Date] nvarchar(25) '$.Date',
   StartTime nvarchar(25) '$.TimeSheet[0].StartTime',
   FinishTime nvarchar(25) '$.TimeSheet[0].FinishTime',
   BreakStart nvarchar(25) '$.TimeSheet[0].BreakTimes[0]',
   BreakFinish nvarchar(25) '$.TimeSheet[0].BreakTimes[1]',
   EmployeeId int '$.TimeSheet[0].EmployeeId',
   Notes nvarchar(max) '$.TimeSheet[0].Notes'
) j

That works perfectly. Thank you so much - but how do I include the break times as well - and there could be multiple? — MichaelIre, Jun 03 '19 at 07:17

Unable to parse string using OPENJSON

1 Answers1