data JSON, INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin)) ); INSERT INTO employees VALUES (’{ "name": "james", "salary": 9000 }’), (’{ "name": "James", "salary": 10000 }’), (’{ "name": "Mary", "salary": 12000 }’), (’{ "name": "Peter", "salary": 8000 }’); SELECT * FROM employees WHERE data->>’$.name’ = ’James’; 

The ->>  operator is the same as JSON_UNQUOTE(JSON_EXTRACT(...)) , and JSON_UNQUOTE() returns a string with collation utf8mb4_bin . The comparison is thus case-sensitive, and only one row matches: 

+------------------------------------+ | data | +------------------------------------+ | {"name": "James", "salary": 10000} | +------------------------------------+ 

•Solution 2. Specify the full expression in the query: 

CREATE TABLE employees ( data JSON, INDEX idx ((CAST(data->>"$.name" AS CHAR(30)))) ); INSERT INTO employees VALUES (’{ "name": "james", "salary": 9000 }’), (’{ "name": "James", "salary": 10000 }’), (’{ "name": "Mary", "salary": 12000 }’), (’{ "name": "Peter", "salary": 8000 }’); SELECT * FROM employees WHERE CAST(data->>’$.name’ AS CHAR(30)) = ’James’; 

CAST()  returns a string with collation utf8mb4_0900_ai_ci , so the comparison case-insensitive and two rows match: 

+------------------------------------+ | data | +------------------------------------+ | {"name": "james", "salary": 9000} | | {"name": "James", "salary": 10000} | +------------------------------------+ 

Be aware that although the optimizer supports automatically stripping CAST()  with indexed generated columns, the following approach does not work because it produces a different result with and without an index (Bug#27337092): 

mysql> CREATE TABLE employees ( data JSON, generated_col VARCHAR(30) AS (CAST(data->>’$.name’ AS CHAR(30))) ); Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> INSERT INTO employees (data) VALUES (’{"name": "james"}’), (’{"name": "James"}’); Query OK, 2 rows affected, 1 warning (0.01 sec) Records: 2 Duplicates: 0 Warnings: 1 mysql> SELECT * FROM employees WHERE data->>’$.name’ = ’James’; +-------------------+---------------+ | data | generated_col | +-------------------+---------------+ | {"name": "James"} | James | +-------------------+---------------+ 1 row in set (0.00 sec) 

