Mysql Join on Json Type field

Question

I am kind of new in MYSQL and stuck in below problem

I have two tables Users and Groups and have stored group ids in user table like this:

{"ids": ["2", "4"]}

And I am trying to Join Groups table with User to get Group name from it.

I have tried somthing like this:

SELECT user.name, groups.name
  FROM user
  LEFT JOIN groups 
    ON JSON_CONTAINS(user.group_ids->'$.ids[*]', CAST(groups.id AS JSON))

but its not working, Please help on this.

Thanks

I think already answered into the below link: stackoverflow.com/questions/39818296/… — Akash prajapati
– Akash prajapati, Commented May 18, 2020 at 18:03

GMB · Accepted Answer · 2020-05-18 19:16:27Z

1

You could use JSON_SEARCH() for this:

SELECT u.name, g.name 
FROM users u
LEFT JOIN groups g 
    ON JSON_SEARCH(u.group_ids -> '$.ids', 'one', g.id)

answered May 18, 2020 at 18:02

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

JSON_SEARCH() cannot return zero or empty string (only non-empty string or NULL return is possible), so IS NOT NULL compare is excess.

@Akina: agreed. I modified my answer.