MySQL with special character in the json path

user_4320184 0
Viewed 557

I have a column in mysql which stores a column with json files and the the key of the json can contain any unicode characters. I have a query to calculate the cardinality of the specific key

SELECT COUNT(DISTINCT JSON_UNQUOTE(JSON_EXTRACT(dimensions, '$.*abc')))from `definition`

the name is *abc which contains a special character * and used in the json path to the value of the json. I can ensure I have the json file with key *abc in the mysql database by running select all.

so how could I query the json path with special characters? from https://dev.mysql.com/doc/refman/8.0/en/string-literals.html, * should not be escaped.

mysql-json mysql
1 Answers

You can use special characters in key names by delimiting them with "":

mysql> set @j = '{"*abc": 123}';

mysql> select json_extract(cast(@j as json), '$."*abc"') as special;
+---------+
| special |
+---------+
| 123     |
+---------+
Bill_Karwin_208600
Related