Skip to content

Instantly share code, notes, and snippets.

@Mevrael
Last active January 11, 2024 16:58
Show Gist options
  • Save Mevrael/bc645cb63a2d3bbae3fb43366b47f313 to your computer and use it in GitHub Desktop.
Save Mevrael/bc645cb63a2d3bbae3fb43366b47f313 to your computer and use it in GitHub Desktop.
MySQL 5.7 JOIN on JSON column of IDs
# Example for MySQL 5.7 how to use JOIN on 2 tables without junction table using new JSON column type.
# Let say we have 2 tables: posts and users
# Users may like posts
# We store the IDs of users who liked each post in posts.liked column which is a JSON array
# which might have a content like "[1, 2, 5, 10]"
SELECT posts.id AS post_id, users.id AS liked_by_user_id FROM posts JOIN users ON JSON_CONTAINS(posts.liked, CAST(users.id AS CHAR))
@donpaul120
Copy link

I'm trying to do something similar but in my case the like value is like
[{"id":1, date_liked:date}, {id:2, date_liked:date}]

I tried doing this
LEFT JOIN users g on JSON_CONTAINS(posts.liked, CAST(g.id as JSON), '$[*].id')

But it says :

In this situation, path expressions may not contain the * and ** tokens or an array range.

Do you have any idea how this can be done? Please

@donpaul120
Copy link

I think I figured it out.. just incase

LEFT JOIN users g on JSON_CONTAINS(posts.liked->'$[*].id', CAST(g.id as JSON))

@asciirobot
Copy link

@stevenwadejr
Copy link

I was just stuck on this and this gist had exactly what I needed to fix it. Thanks so much!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment