Escaping characters in T-SQL OPENJSON queries

Question

I have the following JSON Data

DECLARE @jsonData NVARCHAR(MAX)
SET @jsonData =
'{  
   "insertions":[  
      {  
         "id":"58735A79-DEA8-462B-B3EB-C2797CA9D44E",
         "last-modified":"2017-08-08 13:07:32",
         "label":"HelloWorld1"
      },
      {  
         "id":"00565BCD-4240-46CF-A48F-849CB5A8114F",
         "last-modified":"2017-08-08 13:11:38",
         "label":"HelloWorld12"
      }
   ]
}'

And trying to perform a select from it:

SELECT
    *
FROM
    OPENJSON(JSON_QUERY(@jsonData,'$.insertions'))
WITH
    (uuid UNIQUEIDENTIFIER '$.id',
    modified DATETIME '$.last-modified',
    Label NVARCHAR(128) '$.label'
    )

It doesn't like the dash in the last-modified field.

Msg 13607, Level 16, State 4, Line 18
JSON path is not properly formatted. Unexpected character '-' is found at position 6.

Is there a way to escape the dash in the query? Everything works fine if there is no dash.

As required to support JSON, I'm using SQL Server 2016 with compatibility level = 130

John · Accepted Answer · 2017-08-25 01:36:33Z

15

Adding double quotes around the field name seems to work

SELECT
    *
FROM
    OPENJSON(JSON_QUERY(@jsonData,'$.insertions'))
WITH
    (uuid UNIQUEIDENTIFIER '$.id',
    modified DATETIME '$."last-modified"',
    Label NVARCHAR(128) '$.label'
    )

answered Aug 25, 2017 at 1:36

John

5,8548 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tristram Over a year ago

Thank you very much. I looked at many google links before this one.

Collectives™ on Stack Overflow

Escaping characters in T-SQL OPENJSON queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related