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

Question

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?

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. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented Jun 11, 2023 at 2:34

Tangentially Perpendicular · Accepted Answer · 2023-06-11 03:08:08Z

3

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

answered Jun 11, 2023 at 3:08

Tangentially Perpendicular

5,4364 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1191247 Over a year ago

You need to index player_inventories in both directions, and one of them should be PK. primary key (player_id, class_id), index (class_id, player_id).

free2idol1 Over a year ago

@user1191247 thanks but any reason why that composite index must be in reverse direction compared to primary key?

user1191247 Over a year ago

@free2idol1 Typically you will want to query the data from both directions. Sometimes classes per player, and other times players per class. This is a generalisation, but more often than not, you will need to index junction tables in both directions.

Venkat · Accepted Answer · 2023-06-11 02:31:52Z

1

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;

answered Jun 11, 2023 at 2:31

Venkat

5712 silver badges6 bronze badges

Comments

SelVazi · Accepted Answer · 2023-06-11 09:08:28Z

1

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

answered Jun 11, 2023 at 9:08

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related