Using MySQL JSON field to join on a table

Question

I have a json field that stores a list of ids (not best practice here I know), I want to know if it's possible to use do operations on this JSON field and use them in the sql.

Below is a fictitious example of what I'm trying to achieve, is something like this doable?

CREATE TABLE user (     user_id INT,     user_name VARCHAR(50),     user_groups JSON );  CREATE TABLE user_group (     user_group_id INT,     group_name VARCHAR(50) );  INSERT INTO user_group (user_group_id, group_name) VALUES (1, 'Group A'); INSERT INTO user_group (user_group_id, group_name) VALUES (2, 'Group B'); INSERT INTO user_group (user_group_id, group_name) VALUES (3, 'Group C');  INSERT INTO user (user_id, user_name, user_groups) VALUES (101, 'John', '[1,3]'); 

With the above data I would like to fashion a query that gives me the results like this:

user_id | user_name | user_group_id | group_name| ------------------------------------------------- 101     | John      | 1             | Group A 101     | John      | 3             | Group C 

Some psuedo style SQL I'm thinking is below, though I still have no clue if this is possible, or what JSON functions mysql offers I would use to achieve this

 SELECT         u.user_id,         u.user_name,         g.user_group_id        g.group_name    FROM users u    LEFT JOIN user_group g on g.user_group_id in some_json_function?(u.user_groups) 

Let me know if the question isn't clear.

Answer 1

With the help of Feras's comment and some fiddling:

  SELECT         u.user_id,         u.user_name,         g.user_group_id,        g.group_name    FROM user u    LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$') 

This appears to work, let me know if there's a better way.