MYSQL query with REGEXP - make-it is not greedy

Question

I need regex help for a MYSQL query to find the rows that contain the cell with the exact pattern. I am new to MYSQL regex.

This is an example table called test_table (json_value is a json array string)

|id |    json_value                                                                                 
-----------------------------------------------------------------------------------------------
| 1 | {"field_198":false,"field_4":"From quality office","field_9":"product with high quality","field_10":"comment"}    
| 2 | {"field_198":true,"field_4":"From ordering office","field_9":"back to quality office","field_10":"comment"}   
| 3 | {"field_198":true,"field_4":"From ordering office","field_9":"cancelled","field_10":"comment"}                    
| 4 | {"field_198":true,"field_4":"Return to quality office","field_9":"product ok","field_10":"comment"}

If I want to get all rows with:

- field_4 containing "quality" string, the query should to return id 1 and 4
- field_9 containing "quality" string, the query should to return id 1 and 2
- field_4 containing "ordering" string, the query should to return id 2 and 3

I hope this example does not matter.

I tried using this query

SELECT id from test_table WHERE json_value REGEXP 'field_4":".*quality.*';

but return id 1, 2 and 4 because it is greedy and find the "quality" in field_9 from line 2

Another request (I know this is a stupid regex)

SELECT id from test_table WHERE json_value REGEXP 'field_4":"[^quality]*quality.*';

but returns only id 1

I read a lot of posts on the Internet, but did not succeed. What should be the regular expression for the correct lines?

, , - , - , "% substring%"

Answer 1

, :

SELECT id from test_table WHERE json_value REGEXP 'field_4":"[^"]*quality';

, . , JSON / , :

+----+----------------------------------------------------------------------------------------------------------------+
| id | json_value                                                                                                     |
+----+----------------------------------------------------------------------------------------------------------------+
|  1 | {"field_198":false,"field_4":"From quality office","field_9":"product with high quality","field_10":"comment"} |
|  2 | {"field_198":true,"field_4":"From ordering office","field_9":"back to quality office","field_10":"comment"}    |
|  3 | {"field_198":true,"field_4":"From ordering office","field_9":"cancelled","field_10":"comment"}                 |
|  4 | {"field_198":true,"field_4":"Return to quality office","field_9":"product ok","field_10":"comment"}            |
|  5 | {"field_198":true,"field_4":"Return to \"quality\" office","field_9":"product ok","field_10":"comment"}        |
+----+----------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT id from test_table WHERE json_value REGEXP 'field_4":"[^"]*quality';
+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.01 sec)

, , id=5, . , , , , .