DELIMITER ;; DROP FUNCTION IF EXISTS count_matching_tags ;; CREATE FUNCTION count_matching_tags (haystack TEXT, tags JSON) RETURNS INT DETERMINISTIC BEGIN DECLARE matches INT; DECLARE idx INT; SET matches = 0; SET idx = 0; WHILE idx < JSON_LENGTH(tags) DO SET matches = IF (INSTR(haystack, JSON_UNQUOTE(JSON_EXTRACT(tags, CONCAT('$[', idx, ']')))), matches + 1, matches); SET idx = idx + 1; END WHILE; RETURN matches; END ;; DROP FUNCTION IF EXISTS get_matching_league ;; CREATE FUNCTION get_matching_league (haystack TEXT, id_sport INT) RETURNS INT DETERMINISTIC BEGIN DECLARE lid INT; SELECT id INTO lid FROM leagues WHERE ( leagues.id_sport = id_sport AND JSON_TYPE(leagues.tags) = 'ARRAY' AND count_matching_tags(haystack, leagues.tags) > 0 ) ORDER BY degree ASC LIMIT 1; RETURN lid; END ;;