MYSQL/JSON: compare an array with the list of a column in JSON format and count the number of terms that match

I want to create a simple search engine that can count the number of properties that match on a user.

I am using a field in JSON format that lists the properties, and when the user enters a search it gets fetched as an array.

I would like to compare the input array with the number of properties that match in the JSON list.

And return the whole in two columns: the first which contains the number of tags which match. And the second which contains the list of tags that have matched.

Here is a real life example of what I’m trying to do:

I guess I have to use a combination of JSON_SEARCH and COUNT, but I can’t seem to find the correct syntax …

Answer

One option would be picking(JSON_EXTRACT()) the members of the match_list array iteratively within whole keys(JSON_KEYS()) of the JSON, starting from the first member(index-0) upto the length(JSON_LENGTH()) of the array in order to look up(JSON_CONTAINS()) whether exists within the JSON column (tags). I’ve used information_schema.tables in order to create an iteration as in the following query :

SET @match_list = '["talkative","cooker"]';

SELECT COALESCE( SUM( matched), 0 ) AS match_count
  FROM 
  (
    SELECT  @i := @i + 1 AS rn,
            JSON_CONTAINS(JSON_KEYS(tags), 
                          JSON_EXTRACT(@match_list, CONCAT('$[',@i-1,']')),
                          '$') AS matched
      FROM information_schema.tables 
     CROSS JOIN t 
     CROSS JOIN (SELECT @i := 0) r
     WHERE @i < JSON_LENGTH(@match_list)
  ) q;

 match_count
 -----------
          2

Demo