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, ']', '' ), '[', ''), '"', ''), ' ', '') )