MYSQL Search JSON Values: how to search value from another table without hard coding index?

I have 2 tables in mysql database as followed:

character_classes table:

+--------+----------+
| id | name |
+--------+----------+
| CLA001 | assassin |
| CLA002 | knight |
| CLA003 | vanguard |
+--------+----------+

player_inventories table:

+--------------+----------------------+
| player_id | character_class |
+--------------+----------------------+
| UID000000001 | ["CLA001"] |
| UID000000002 | ["CLA001", "CLA002"] |
| UID000000003 | ["CLA001", "CLA002", "CLA003"] |
+--------------+----------------------+

I am trying to join the player_inventories tbl to ``character_classesto getcharacter_class's names from character_classes` table:

SELECT player_id, (SELECT CONCAT_WS(
', ',
(select name from character_classes where id = JSON_EXTRACT( character_class, '$[0]') ),
(select name from character_classes where id = JSON_EXTRACT( character_class, '$[1]') ),
(select name from character_classes where id = JSON_EXTRACT( character_class, '$[2]') )
) ) as character_class_name
from player_inventories;

But the issue is the number of json items in character_class field at player_inventories tbl is varied, it can be 1,2 or 3 or even more so I need to hard code the index, i.e $[0], $[1]and $[2] to get its corresponding name.

Is there any way to improve that query so that I can get all character class' names on the fly without hard coding the index?

Solution

The other way without hard-coding the index, you can extract all character class names from the player_inventories table by using the JSON_CONTAINS function with a subquery to match the character_classes table. The names should then be combined using GROUP_CONCAT into a single column. Use GROUP BY to group the results by player_id.

SELECT
    pi.player_id,
    GROUP_CONCAT(cc.name SEPARATOR ', ') AS character_class_names
FROM
    player_inventories pi
JOIN
    character_classes cc ON JSON_CONTAINS(pi.character_class, CONCAT('"', cc.id, '"'))
GROUP BY
    pi.player_id;