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

Question

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 ...

Barbaros Özhan · Accepted Answer · 2020-10-14 18:51:35Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related