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.
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"]
Is this a bug? Is there a work-around to extract the value of “60SecInches”?
I have a database with around 1 million records containing this key.
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.
Mostly I have been using PHP json_decode to access this data which works fine. Recently I have been using MySQL JSON functions to review and audit values in this database and ran into this issue. Because of the amount of historical data, changing the key would be time-consuming.