Question 183768 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

3

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    Don’t store the class data as a JSON . Create a many to many table that maps player ID to class ID, then you can use a JOIN in your query to retrieve all the class names with having to use an array index.

    This also makes it much easier to query on the classes. Finding all the Wizards in a table is a much easier query than searching JSON arrays.

    For example:

    create table players (
  id int auto_increment primary key,
  name varchar(45)
  );
create table player_inventories(
  player_id int,
  class_id int,
   index (player_id));
create table character_classes (
  id int auto_increment primary key,
  name varchar(45)
  );
  
  Insert players (name) values ('Alice'),('Bob'),('Charles'),('Dave'),('Eve');
  insert character_classes (id,name) values (1,'Wizard'),(2,'Elf'),(3,'Goblin'),(4,'Elder');
  insert player_inventories (player_id, class_id) values (1,1),(1,2),(1,3),(2,3),(2,4)

    Query #1

    select players.name, group_concat(cc.name) as classes from players 
    join player_inventories pi ON players.id = pi.player_id
    join character_classes cc on pi.class_id = cc.id
    group by players.name;
    name classes
    Alice Elf,Goblin,Wizard
    Bob Elder,Goblin

    View on DB Fiddle

    Login or Signup to reply.
  3. 0

    Consider @Tangential’s answer, and if changing table structures isn’t an option, try this solution:

    First convert JSON array into rows using JSON_TABLE, then use GROUP BY and the aggregate function GROUP_CONCAT to get the expected output :

    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

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search