MYSQL:在给定字符串JSON_ARRAY的情况下查找元素

Question

我试图找出一种方法来查找给定字符串JSON_ARRAY的元素。我想要实现的是一个contains all功能。

假设我有一个名为Movies的表，其中包含列name和genre，还有一个名为Genre的表，其中包含列id。

电影

NAME      GENRE
Batman    1 
Batman    2  
Aquaman   2  
Joker     3  
IT        4  

体裁

ID   NAME
1    Drama 
2    Action  
3    Thriller  
4    Horror    

现在，如果我有一个带有JSON_ARRAY的

SET @array = JSON_ARRAY("Action", "Drama");  

如何创建一个给定此JSON_ARRAY返回Batman的存储过程？

我认为解决方案是SQL递归，但我不确定...

SET @array = JSON_ARRAY("Action", "Drama");  
sproc.GetMovieGivenGenres(@array, @out_movie);
SELECT @out_movie; --> Batman

Answer 1

我们可以尝试使用JSON_SEARCH()函数，它具有搜索JSON数组的能力：

SELECT
    m.NAME
FROM Movies m
INNER JOIN Genre g
    ON m.GENRE = g.ID
GROUP BY
    m.NAME
HAVING
    COUNT(CASE WHEN JSON_SEARCH(@array, 'one', g.NAME) IS NOT NULL THEN 1 END) =
    JSON_LENGTH(@array);

对于每部电影，如果在输入数组中找到给定的流派，则JSON_SEARCH函数将返回一个非NULL值。匹配的电影是包含输入数组中所有流派的电影。

Answer 2

在JSON 8中，您可以使用JSON_TABLE()将mySQL数组转换为表。然后您可以将生成的表与您的两个表连接起来：

SET @array = JSON_ARRAY("Action", "Drama");  

select m.NAME
from json_table(
  @array,
  '$[*]' columns (genre_name varchar(50) path '$')
) j
join Genre g on g.NAME = j.genre_name
join Movies m on m.GENRE = g.ID
group by m.NAME
having count(*) = json_length(@array);

db-fiddle demo

这样，MySQL将能够在Genre(NAME)上使用索引。