Mysql: Use JSON field to join 2 tables

Question

I have 2 tables: Users table

id | groups    |
---------------
1  | ["1","2"] |     
2  | ["2"]     | 

Groups table

id | name | 
-----------
1  | Test | 
2  | Jest | 

I need to fetch the records from groups table, given an ID from the users table. Mostly single line SQL queries is preferred, but if not possible I can work with a stored procedure too.

I have found a way where there are no quotes for each ID's here. But I need the query to work with quotes.

Mysql version used is 5.7

Answer 1

Use JSON_search:

select u.id as "user id", g.id as "groups id", g.name as "group name"
from Users u
  join `Groups` g on JSON_search(u.groups, 'ALL', g.id);

See dbfiddle.

In MySQL 5.7 you can use FIND_IN_SET which will be slower but works:

select u.id as "user id", g.id as "groups id", g.name as "group name"
from Users u
  join `Groups` g on find_in_set( g.id, replace(replace(replace(replace(u.groups, ']', '' ), '[', ''), '"', ''), ' ', '') )

Answer 2

You have to create a many to many relationship to normalize your data.

CREATE TABLE user (
    id INT UNSIGNED AUTO_INCREMENT KEY
    ,name VARCHAR (255) NOT NULL
);

CREATE TABLE group (
    id INT UNSIGNED AUTO_INCREMENT KEY
    ,name VARCHAR (255) NOT NULL
);

CREATE TABLE group_and_user (
    group INT UNSIGNED
    ,user INT UNSIGNED
    ,PRIMARY KEY (group,user)
    ,FOREIGN KEY (group)
        REFERENCES group (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    ,FOREIGN KEY (user)
        REFERENCES user (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The third table will have the vector you want.

Answer 3

This task is awkward on MySQL 5.7 because it lacks a way to "explode" a JSON array into rows.

Exploding a JSON array can be done with a MySQL 8.0 function, JSON_TABLE() but that function is not implemented in MySQL 5.7 (which to be fair, is now past its end of life after October 2023).

So you have to extract each of the elements of the JSON array one by one, using a set of integer values in a JSON path like '$[0]', '$[1]', and so on. As many integers as the longest JSON array.

create table users (
  id int primary key,
  `groups` json
);

insert into users values
(1, '["1","2"]'),
(2, '["2"]');

create table `groups` (
  id int primary key,
  name varchar(10)
);

insert into `groups` values
(1, 'Test'),
(2, 'Jest');

select u.id, g.name
from (
  select id, json_unquote(json_extract(`groups`, concat('$[', num-1, ']'))) as group_id
  from users
  cross join (
    select 1 as num union select 2 union select 3 union select 4
  ) as numbers
) as u
inner join `groups` as g on u.group_id = g.id;

Demo on MySQL 5.7: https://dbfiddle.uk/wZOVievD

If this seems like a lot of work, or the query is too inefficient, then just understand that this is the penalty for using JSON to store an array of values instead of a normalized table design with one value per row.