1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
dashenswen
  • 540
  • 2
  • 4
  • 21

1 Answers1

2

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
  • 538,548
  • 86
  • 673
  • 828