CODEWITHSUNDEEP
jsonsql-server
Tommy Sharp
Tommy Sharp

Reputation: 95

Dealing with special characters in SQL OPENJSON WITH command?

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)
          )

Upvotes: 2

Views: 1854

Answers (3)

Ben
Ben

Reputation: 2023

Wrap the json field name in double quotes:

Before:

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

After:

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

Upvotes: 0

lptr
lptr

Reputation: 6788

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)
          )

Upvotes: 0

Roger Wolf
Roger Wolf

Reputation: 7692

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;

Upvotes: 6

Related Questions