CODEWITHSUNDEEP
mysqlsqljsonjoinleft-join
CodeEdge
CodeEdge

Reputation: 33

Mysql Join on Json Type field

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

Upvotes: 2

Views: 65

Answers (1)

GMB
GMB

Reputation: 222412

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)

Upvotes: 1

Related Questions