0

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.
Zhorov
  • 28,486
  • 6
  • 27
  • 52
Stephen
  • 23
  • 1
  • 6
  • 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 Mar 16 '21 at 07:23
  • Check [Can I get a javascript object property name that starts with a number?](https://stackoverflow.com/questions/5809790/can-i-get-a-javascript-object-property-name-that-starts-with-a-number). In MDN [Property Accessors](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Property_Accessors) explains the differences between dot and bracket notation – Panagiotis Kanavos Mar 16 '21 at 07:26

1 Answers1

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
Zhorov
  • 28,486
  • 6
  • 27
  • 52