The MySQL table has this format:
CREATE TABLE bar...
...
foo JSON
...
The field foo
has this content:
{"#:8": 0.90189, "#:34": 0.90239, "#:55": 0.90238, "#:144": 0.90219, "X:21-34": -1}
This command fails:
SELECT foo FROM bar WHERE foo->'$.X:21-34' != 0;
Error (4,1): Invalid JSON path expression. The error is around character position 9.
How can I access those fields which have some special chars in the key name but the JSON string is still valid?
I know hyphens also can create complicated mappings when ported between languages. I've seen some JSON deserialize libraries convert those keys to a camelCase style. Example: var something = { "some-value": 'thing' } Vs var something = { "someValue": 'thing', "some_other_value": 'thing_two' }
Keys must be strings, and values must be a valid JSON data type (string, number, object, array, boolean or null). Keys and values are separated by a colon. Each key/value pair is separated by a comma. Values in a JSON object can be another JSON object.
Each key/value pair is separated by a comma. It is a common mistake to call a JSON object literal "a JSON object". JSON cannot be an object. JSON is a string format.
Object Syntax. Example. { "name":"John", "age":30, "car":null } JSON objects are surrounded by curly braces {}. JSON objects are written in key/value pairs. Keys must be strings, and values must be a valid JSON data type (string, number, object, array, boolean or null). Keys and values are separated by a colon.
OMG. It was very simple and is logical. This is the solution:
SELECT foo FROM bar WHERE foo->'$."X:21-34"' != 0;
I have to use double quotes around the key name.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With