CODEWITHSUNDEEP
mysqljson
Peter VARGA
Peter VARGA

Reputation: 5186

Access JSON key containing colon and hyphen

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?

Upvotes: 12

Views: 5914

Answers (1)

Peter VARGA
Peter VARGA

Reputation: 5186

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.

Upvotes: 38

Related Questions