Search code examples
mysqlmysql-json

MySQL with special character in the json path

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.

Solution

  • 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     |
+---------+