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

Revisions

  1. Mevrael revised this gist Nov 10, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion mysql_5.7_join_json_column_id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@

    # Example for MySQL 5.7 how to use JOIN two tables without junction table using new JSON column type.
    # 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
  2. Mevrael revised this gist Nov 10, 2016. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion mysql_5.7_join_json_column_id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,2 +1,8 @@

    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))
    # Example for MySQL 5.7 how to use JOIN two 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))
  3. Mevrael revised this gist Nov 10, 2016. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions mysql_5.7_join_json_column_id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,2 @@
    ```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))
    ```
  4. Mevrael created this gist Nov 10, 2016.
    3 changes: 3 additions & 0 deletions mysql_5.7_join_json_column_id.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,3 @@
    ```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))
    ```