OPENJSON SQL Server Error: Invalid column name 'property-with-dash'

Question

I am working in SQL Server and I Need to select "name-surname" tag from below mentioned JSON but getting following error: JSON path is not properly formatted. Unexpected character '-' is found at position 11.

do we have any good work around to read JSON which has properties with '-'?

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name-surname": "John-TAG","firstname": "John", "lastname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name-surname": "John-CAB","firstname": "Jane", "lastname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    namesurname NVARCHAR(50) '$.info.name-surname',
    firstName NVARCHAR(50) '$.info.firstname',
    lastName NVARCHAR(50) '$.info.lastname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
    );

I have found one but searching better solution: First replace are '-' with '_' from whole JSON string then we can read JSON using OpenJSON

Answer 1

Use double quotes around the name; i.e., $.info."name-surname":

DECLARE @json NVARCHAR(MAX);
SET @json = N'[
  {"id": 2, "info": {"name-surname": "John-TAG","firstname": "John", "lastname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name-surname": "John-CAB","firstname": "Jane", "lastname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    namesurname NVARCHAR(50) '$.info."name-surname"',
    firstName NVARCHAR(50) '$.info.firstname',
    lastName NVARCHAR(50) '$.info.lastname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
    );