MYSQL REGEXP / NOT REGEXP multiple JSON fields
Futzing around with personal projects. Trying to stay relevant. This particular project makes use of JSON support on mysql. I know right? I did it anyways. The following clause was used as filter: Select .… AND ( LOWER( json_unquote(cd.data->'$.title') ) REGEXP ? OR LOWER( json_unquote(cd.data->'$.title.$t') ) REGEXP ? OR ... ) AND ( LOWER( json_unquote(cd.data->'$.title') ) NOT REGEXP ? OR LOWER( json_unquote(cd.data->'$.title.$t') ) NOT REGEXP ? OR ... ) But that was not not performing as expected. After some trial and error. The following solution seems to work best. concat_ws the fields into a string. Select .... AND CONCAT_WS(' ', LOWER( cd.data->'$.text' ), LOWER( cd.data->'$.text.$t' ) ) REGEXP ? AND CONCAT_WS(' ', LOWER( cd.data->'$.text' ), LOWER( cd.data->'$.text.$t' ) ) NOT REGEXP ? Can’t speak for efficiency but is working as expected