MYSQL query with REGEXP - make-it non greedy

Question

I need some help with a regular expression for a MYSQL query to search rows with which contain a cell with an exact pattern. I'm new on MYSQL regex.

This is a sample table named as test_table (json_value is a json string of an array)

|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 the example is concludent.

I have tried use this query

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

but return id 1, 2 and 4 because is greedy and find the "quality" at field_9 from row 2

Another query is (I know, this is a stupid regex)

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

but return only id 1

I have readed many posts on internet but with no succes. How should be the regex to get the proper rows?

EDIT One more think, to be more explicit, first work from search is a full key from array, but the second is partial value, like a "%substring%"

Thank you

Answer 1

This is the best I could come up with:

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

It meets your requirements given the data you provided. It will break, however, if your JSON contains embedded/escaped double-quotes, like:

+----+----------------------------------------------------------------------------------------------------------------+
| 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)

In this case I think you would have wanted the row with id=5 to be returned, but it isn't. But if you have that kind of data, I think you have no choice but to really parse it, and a regex won't do the trick.