Description:
Given a table with a JSON column, with a row with the following value:
{ "description": "hello world" }
Searching for the row via the description attribute that starts with "hello", using LIKE, requires that you include the first double quote in the value. Yet, doing an exact match does not require that the quotes be included.
How to repeat:
1. Create a table with a JSON column:
CREATE TABLE `test_json` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
2. Insert a value with valid JSON:
INSERT INTO `test_json` (data) VALUES('{ "description": "hello world" }');
3. Search for the row with a query that uses a LIKE condition on an attribute of the JSON value:
mysql> select id from test_json where data->'$.description' LIKE "hello %";
Empty set (0,00 sec)
If a double quote is added before the "hello", it works:
mysql> select id from test_json where data->'$.description' LIKE '"hello %';
+----+
| id |
+----+
| 4 |
+----+
1 row in set (0,00 sec)
4. Note that using an equals operator does not require quotes:
mysql> select id from test_json where data->'$.description' = 'hello world';
+----+
| id |
+----+
| 4 |
+----+
1 row in set (0,00 sec)
Suggested fix:
Make the query work consistently when using LIKE vs =.
Description: Given a table with a JSON column, with a row with the following value: { "description": "hello world" } Searching for the row via the description attribute that starts with "hello", using LIKE, requires that you include the first double quote in the value. Yet, doing an exact match does not require that the quotes be included. How to repeat: 1. Create a table with a JSON column: CREATE TABLE `test_json` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; 2. Insert a value with valid JSON: INSERT INTO `test_json` (data) VALUES('{ "description": "hello world" }'); 3. Search for the row with a query that uses a LIKE condition on an attribute of the JSON value: mysql> select id from test_json where data->'$.description' LIKE "hello %"; Empty set (0,00 sec) If a double quote is added before the "hello", it works: mysql> select id from test_json where data->'$.description' LIKE '"hello %'; +----+ | id | +----+ | 4 | +----+ 1 row in set (0,00 sec) 4. Note that using an equals operator does not require quotes: mysql> select id from test_json where data->'$.description' = 'hello world'; +----+ | id | +----+ | 4 | +----+ 1 row in set (0,00 sec) Suggested fix: Make the query work consistently when using LIKE vs =.