用于多值问题的JSON_EXTRACT (使用MYSQL将整数数组中的字符串与整数匹配)

Question

table1：

id  languages
2   ["2"]   
3   ["1"]   
9   ["1","2"]

“语言”字段是varchar(255)。

Table2：

language_id name
1           English
2           Japanese

"language_id“是int(11)。

我希望输出类似于以下内容之一：

预期产出1：

id  languages  name
2   ["2"]      ["Japanese"]
3   ["1"]      ["English"]
9   ["1","2"]  ["English", "Japanese"]

或

预期产出2：

id  languages  name
2   ["2"]      Japanese
3   ["1"]      English
9   ["1","2"]  English, Japanese

我尝试了以下查询，该查询只会关闭：

SELECT t2.name, r.*
FROM table2 t2
LEFT JOIN table1 t1
ON t2.language_id=CAST(json_extract(t1.languages, '$[0]') AS UNSIGNED);

上述查询的输出：

id  languages  name
2   ["2"]      Japanese
3   ["1"]      English
9   ["1","2"]  English

在"name“字段中省略了语言数组的第二部分。

我怎样才能像预期的输出1或2一样输出？

Answer 1

使用JSON_TABLE()将数组拆分为行。然后，您可以使用它来获取所有的名称，并使用GROUP_CONCAT()将它们组合成一个以逗号分隔的字符串。

SELECT t1.id, t1.languages, GROUP_CONCAT(t2.name) AS names
FROM table1 AS t1
JOIN JSON_TABLE(languages, 
                "$[*]" columns (language VARCHAR(9) PATH "$")) AS t1_json
JOIN table2 AS t2 ON t2.language_id = t1_json.language
GROUP BY t1.id

演示