Bug #93881 where clause with JSON column path broken when using LIKE
Submitted: 10 Jan 20:23 Modified: 10 Jan 21:26
Reporter: A P Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0.13 OS:Mac OS X
Assigned to: CPU Architecture:x86

[10 Jan 20:23] A P
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 =.
[10 Jan 20:49] A P
clarify title
[10 Jan 21:26] Miguel Solorzano
Thank you for the bug report.