Skip to content

Instantly share code, notes, and snippets.

@Mevrael

Mevrael/mysql_5.7_join_json_column_id.sql

Last active January 11, 2024 16:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Mevrael/bc645cb63a2d3bbae3fb43366b47f313 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Mevrael/bc645cb63a2d3bbae3fb43366b47f313 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL 5.7 JOIN on JSON column of IDs
Raw
mysql_5.7_join_json_column_id.sql
```mysql
SELECT u1.id AS id1, u2.id AS id2 FROM users AS u1 JOIN users AS u2 ON JSON_CONTAINS(u1.friends, CAST(u2.id AS CHAR))
```
@teol
Copy link

teol commented Jun 21, 2018

Thanks for sharing this

@donpaul120
Copy link

donpaul120 commented Nov 27, 2018

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

donpaul120 commented Nov 27, 2018

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

asciirobot commented Oct 23, 2020

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

@stevenwadejr
Copy link

stevenwadejr commented Jan 11, 2024

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