Styjun

I try to use a regex with mysql that search boundary words in a json array string but I don't want the regex match words order because I don't know them.

So I started firstly to write my regex on regex101 (https://regex101.com/r/wNVyaZ/1) and then try to convert this one for mysql.

WHERE `Wish`.`services` REGEXP '^([^>].*[[:<:]]Hygiène[[:>:]])([^>].*[[:<:]]Radiothérapie[[:>:]]).+';

WHERE `Wish`.`services` REGEXP '^([^>].*[[:<:]]Hygiène[[:>:]])([^>].*[[:<:]]Andrologie[[:>:]]).+';

In the first query I get result, cause "Hygiène" is before "Radiothérapie" but in the second query "Andrologie" is before "Hygiène" and not after like it written in the query. The problem is that the query is generated automatically with a list of services that are choosen with no order importance and I want to match only boundary words if they exists no matter the order they have.

You can search for words in JSON like the following (I tested on MySQL 5.7):

select * from wish 
where json_search(services, 'one', 'Hygiène') is not null 
 and json_search(services, 'one', 'Andrologie') is not null;

+------------------------------------------------------------+
| services |
+------------------------------------------------------------+
| ["Andrologie", "Angiologie", "Hygiène", "Radiothérapie"] |
+------------------------------------------------------------+

See https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-search

If you can, use the JSON search queries (you need a MySQL with JSON support).

If it's advisable, consider changing the database structure and enter the various "words" as a related table. This would allow you much more powerful (and faster) queries.

JOIN has_service AS hh ON (hh.row_id = id)
JOIN services AS ss ON (hh.service_id = ss.id
 AND ss.name IN ('Hygiène', 'Angiologie', ...)

Otherwise, in this context, consider that you're not really doing a regexp search, and you're doing a full table scan anyway (unless MySQL 8.0+ or PerconaDB 5.7+ (not sure) and an index on the full extent of the 'services' column), and several LIKE queries will actually cost you less:

WHERE (services LIKE '%"Hygiène"%'
 OR services LIKE '%"Angiologie"%'
 ...)

or

 IF(services LIKE '%"Hygiène"%', 1, 0)
+IF(services LIKE '%"Angiologie"%', 1, 0)
+ ... AS score

HAVING score > 0 -- or score=5 if you want only matches on all full five
ORDER BY score DESC;