SELECT JSON_VALUE('{"dateTime": "2024-07-21 08:11:07 -0600", "60SecInches": 0.00, "DailyInches": 0.01, "TotalInches": 10.61}', "$.60SecInches");
MySQL said: #3143 – Invalid JSON path expression. The error is around character position 13. That seems to point to the end of this string.
"$.60SecInches"
If I remove the “60” from the key, the above SELECT works. But the following works as well.
SELECT JSON_KEYS('{"dateTime": "2024-07-21 08:11:07 -0600", "60SecInches": 0.00, "DailyInches": 0.01, "TotalInches": 10.61}');
["dateTime", "60SecInches", "DailyInches", "TotalInches"]
According to JSON.org, a string is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes. Any valid string can be used as a JSON key.
I tried backslashes in various places, but that did not work.
The solution is the key name must be separately enclosed in double-quotes and the whole path must be enclosed with single-quotes as
'$."60SecInches"'
This can be used for other “special characters” as well. For example “*” or “#” or number only keys. I am not sure why a number is a special character, but it seems to be in this case.