Search code examples
mysqlsqljsonjson-extract

MySQL searching JSON array for value, using `like` or regexp

I have a table with a field called data_json.

An example of the JSON shape is below:

{
  title: "My Title",
  arr: ["hello there", "foobar", "foo hello bar"]
}

I'd simply like to find rows where data_json->'$.arr' contains a value, using regexp, or like.

Eg:

select * from mytable where ??? like '%hello%';
Solution

  • Need to extract the elements of the array. To perform this JSON_EXTRACT() function might be used along with index values upto the length of it. Row generation methods through metadata tables of information_schema, such like below, might be used to generate those index values :

    SELECT value
  FROM 
  (
    SELECT  @i := @i + 1 AS rn,
           JSON_UNQUOTE(JSON_EXTRACT(data_json, CONCAT('$.arr[',@i-1,']'))) AS value              
      FROM information_schema.tables 
     CROSS JOIN mytable 
     CROSS JOIN (SELECT @i := 0) r
  ) q
 WHERE value LIKE '%hello%'

    Demo