jpmottin jpmottin - 3 months ago 8
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
"Aztalan"
.

But this one doesn't work :

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


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

Thanks in advance for your helps.

Answer

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('{"user.id": 14, "user.name": "Aztalan"}', '$.user\.name');