Parsing JSON in SQL Server with JSON attribute having a number in its name

Question

I am trying to parse a JSON file that is stored in a column of a SQL server table. I am using the JSON_VALUE function. But this function does not like it that the attribute (called 6Months) has a number in its name. And it is giving me an error for that reason. Is there a way to force the function read the file? Please see query and error message below. I greatly appreciate any help.

Select 
JSON_VALUE(JsonFile, '$.company.6Months.count') 
From dbo.Filestore

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

You should seriously rethink the attribute name. Most languages disallow names that start with numbers, not just T-SQL. Even JavaScript doesn't allow numbers at the start when you use dot notation — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 16, 2021 at 7:23
Check Can I get a javascript object property name that starts with a number?. In MDN Property Accessors explains the differences between dot and bracket notation — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 16, 2021 at 7:26

Zhorov · Accepted Answer · 2021-03-16 07:21:11Z

3

You need to use quotes in your path expression. As is mentioned in the documentation, if the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes.

SELECT JSON_VALUE(JsonFile, '$."company.6Months.count"') 
FROM dbo.Filestore

edited Mar 16, 2021 at 7:21

answered Mar 16, 2021 at 7:18

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

2 Comments

Stephen Over a year ago

Thanks a lot Zhorov, that solved it for me.

Panagiotis Kanavos Over a year ago

@Stephen you should consider changing that name. Even JavaScript would choke on 6Months if you used dot notation, ie myObject.6Months

Collectives™ on Stack Overflow

Parsing JSON in SQL Server with JSON attribute having a number in its name

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related