Using MySQL JSON field to join on a table

by

With the help of Feras’s comment and some fiddling:

  SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')

This appears to work, let me know if there’s a better way.

More Related Contents:

  1. How to search JSON data in MySQL?
  2. MySQL JSON: How to find object from key value
  3. How to update JSON data type column in MySQL 5.7.10?
  4. MySQL search json value by key in array
  5. How to convert result table to JSON array in MySQL
  6. Storing Data in MySQL as JSON
  7. How do I generate nested json objects using mysql native json functions?
  8. How can write queries in MySQL that can parse JSON data in a column?
  9. Convert JSON array in MySQL to rows
  10. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  11. How to retrieve JSON data from MySQL?
  12. How to create and insert a JSON object using MySQL queries?
  13. MySQL vs. JSON – Why? [closed]
  14. How to search JSON array in MySQL?
  15. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0
  16. How To have Dynamic SQL in MySQL Stored Procedure
  17. mysql Foreign key constraint is incorrectly formed error
  18. Unknown Column In Where Clause
  19. Left Join not returning all rows
  20. MySQL match() against() – order by relevance and column?
  21. Maximum length for MySQL type text
  22. gem install: Failed to build gem native extension (can’t find header files)
  23. Entity-Attribute-Value Table Design
  24. Filter by COUNT(*)?
  25. SQL Group By with an Order By
  26. How to Convert UTC Date To Local time Zone in MySql Select Query
  27. update a column by subtracting a value
  28. Insert data into table with result from another select query
  29. MySQL LIKE vs LOCATE
  30. Should I always prefer MySQL InnoDB over MyISAM?

Leave a Comment