Accessing JSON_VALUE in MS SQL Server with numeric property names

Question

I'm trying to return a value in some JSON data stored in a SQL Server table. Except I'm working with some very particular JSON data - it's DICOM (medical imaging standard). In the DICOM Standard for JSON, the actual property names in the JSON text are numbers (they're DICOM tag numbers). So, for example, there's a tag (0010,0020) which contains the patient ID. In JSON, it looks like this:

{
"00100020":
    {
    "vr":"LO",
    "Value":"123456789"
    }
}

Normally, I'd access this by executing the following query (table name is "Instance"):

SELECT JSON_Value(JSONData,'$.00100020.Value') AS PatientID FROM Instance

But that doesn't work with MSSQL. I get this error:

Msg 13607, Level 16, State 4, Line 1
JSON path is not properly formatted. Unexpected character '0' is found at position 2.

What I can do is go edit that JSON text to look like this (notice all I am doing differently is adding an "A" prefix to the property name)...

{
"A00100020":
    {
    "vr":"LO",
    "Value":"123456789"
    }
}

And after doing that, I am able to run this query and return results successfully:

SELECT JSON_Value(JSONData,'$.A00100020.Value') AS PatientID FROM Instance

The only difference is that the property name has an alphabetic character in it, instead of all numeric characters.

I can't change the format of this JSON data in actual practice. The property name is numeric, and that's just the way it's going to be. It seems like SQL Server wants to treat the property name differently if it only contains numbers. Is there a way to force SQL to recognize a JSON property name like "00100020" as an actual property name (string) instead of a number I'm throwing into the mix?

Thanks a bunch in advance for any help!

Answer 1

Use double quotes in your JSON Path expression. eg:

declare @doc nvarchar(max) = 
'{
"00100020":
    {
    "vr":"LO",
    "Value":"123456789"
    }
}';

SELECT JSON_Value(@doc,'$."00100020".Value') AS PatientID