jpmottin jpmottin - 1 year ago 110
JSON Question

(MySQL) How to extact a json field with a dot inside (special character)

First of all, I am using MySQL v5.7 .

Now, here is the problem...
This following example works (provided by Official MySQL website) :

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

And it returns

But this one doesn't work :

SELECT JSON_EXTRACT('{"": 14, "": "Aztalan"}', '$');

How can i get the "" json property name with json_extract function ?

Thanks in advance for your helps.

Answer Source

From the MySQL 5.7 Reference Manual

To specify a literal % or _ character in the search string, 
precede it by the escape character. The default is \ if 
the escape_char argument is missing or NULL. Otherwise, 
escape_char must be a constant that is empty or one character.

It doesn't specify the '.' as being a special character, but try a backslash in front of the dot and see if that improves your situation.

So, your second call would look like:

SELECT JSON_EXTRACT('{"": 14, "": "Aztalan"}', '$.user\.name');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download