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!

It seems to work okay if you quote the identifier: SELECT JSON_VALUE(JSONData, '$."00100020".Value') AS PatientId FROM Instance; ...but until you posted this question, I didn't even know SQL Server had a JSON_VALUE function, so I'll leave it to someone who knows better to explain why :D (The docs say you should quote if the name starts with a dollar or contains "special characters such as spaces", but numbers don't feel that special to me...) — Matt Gibson
– Matt Gibson, Commented May 31, 2018 at 21:52

David Browne - Microsoft · Accepted Answer · 2018-05-31 21:56:31Z

11

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

answered May 31, 2018 at 21:56

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Accessing JSON_VALUE in MS SQL Server with numeric property names

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related