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: https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table — Akash prajapati, May 18 '20 at 18:03

GMB · Accepted Answer · 2020-05-18T19:16:27.903

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)

edited May 18 '20 at 19:16

answered May 18 '20 at 18:02

GMB

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 May 18 '20 at 18:33
@Akina: agreed. I modified my answer. – GMB May 18 '20 at 19:16

1 Answers1