By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table character_classes (
id varchar(20),
name varchar(20)
);
insert into character_classes values
('CLA001', 'assassin'),
('CLA002', 'knight'),
('CLA003', 'vanguard');
create table player_inventories(
player_id varchar(20),
character_class json
);
insert into player_inventories values
('UID000000001', '["CLA001"]'),
('UID000000002', '["CLA001", "CLA002"]'),
('UID000000003', '["CLA001", "CLA002", "CLA003"]');
Records: 3 Duplicates: 0 Warnings: 0
Records: 3 Duplicates: 0 Warnings: 0
SELECT p.player_id, cc.name
FROM player_inventories p,
JSON_TABLE( character_class, "$[*]"
COLUMNS( element text PATH "$" )
) s
INNER JOIN character_classes as cc on cc.id = s.element
| player_id | name |
|---|---|
| UID000000003 | assassin |
| UID000000002 | assassin |
| UID000000001 | assassin |
| UID000000003 | knight |
| UID000000002 | knight |
| UID000000003 | vanguard |
SELECT p.player_id, GROUP_CONCAT(cc.name SEPARATOR ', ') AS character_class_name
FROM player_inventories p,
JSON_TABLE( character_class, "$[*]"
COLUMNS( element text PATH "$" )
) d
INNER JOIN character_classes as cc on cc.id = d.element
GROUP BY p.player_id
| player_id | character_class_name |
|---|---|
| UID000000001 | assassin |
| UID000000002 | assassin, knight |
| UID000000003 | assassin, knight, vanguard |
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;
| player_id | character_class_name |
|---|---|
| UID000000001 | assassin |
| UID000000002 | assassin, knight |
| UID000000003 | assassin, knight, vanguard |