Dealing with special characters in SQL OPENJSON WITH command?

Question

I have a JSON string that I am trying to import into my SQL Server database using the OPENJSON WITH but I am having issues because I think the JSON "field names" contain a forward slash.

What else would I need to do in order to get the start and end values? At the moment I just get NULLS...

DECLARE @JSONText NVarChar(max) = '[{
    "Labour Resource Name": "ABC Consulting",
    "Start Date/Time": "2020-07-06T06:30:00",
    "End Date/Time": "2020-07-06T10:30:00"
}]'

SELECT * 
FROM OPENJSON (@JSONText)
     WITH ([Labour Resource Name] NVarChar(512),
           [Start Date/Time] NVarChar(50),
           [End Date/Time] NVarChar(50)
          ) 

Answer 1

You don't have to mangle your output column aliases, instead you can specify JSON paths for all of them:

DECLARE @JSONText NVarChar(max) = '[{
    "Labour Resource Name": "ABC Consulting",
    "Start Date/Time": "2020-07-06T06:30:00",
    "End Date/Time": "2020-07-06T10:30:00"
}]';

SELECT j.* 
FROM OPENJSON (@JSONText)
WITH (
    [LabourResourceName] nvarchar(512) '$."Labour Resource Name"',
    [StartDateTime] nvarchar(50) '$."Start Date/Time"',
    [EndDateTime] nvarchar(50) '$."End Date/Time"'
) j;

Answer 2

DECLARE @JSONText NVarChar(max) = '[{
    "Labour Resource Name": "ABC Consulting",
    "Start Date/Time": "2020-07-06T06:30:00",
    "End Date/Time": "2020-07-06T10:30:00"
}]'

SELECT * 
FROM OPENJSON (REPLACE(@JSONText, N'Date/Time', N'Date\/Time'))
     WITH ([Labour Resource Name] NVarChar(512),
           [Start Date/Time] NVarChar(50),
           [End Date/Time] NVarChar(50)
          ) 

Answer 3

Wrap the json field name in double quotes:

Before:

ecommerce nvarchar(max) '$.special-characters',

After:

ecommerce nvarchar(max) '$."special-characters"',