MySQL 5.7 JOIN on JSON column of IDs

mysql_5.7_join_json_column_id.sql

# 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))

Thanks for sharing this

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

I think I figured it out.. just incase

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

There is a useful discussion with more examples here:
https://stackoverflow.com/questions/39818296/using-mysql-json-field-to-join-on-a-table

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