MySQL, Select records based on values in JSON array
You can use the following solution, using JSON_CONTAINS:
SELECT * FROM UsersWHERE JSON_CONTAINS(interestIds, '2') = 1;The third (optional) paramater path gives you the posibility to use this function only on a specific part of your JSON value. So the following example checks if 2 is the second value of the array:
SELECT *FROM testWHERE JSON_CONTAINS(interestIds, '2', '$[1]') = 1;
Use JSON_SEARCH which returns path to element you are searching, or null if not found:
SELECT *FROM usersWHERE JSON_SEARCH(interestids, 'one', '2') IS NOT NULLIf you're storing many-to-many relationship using simple JSON array, there are better ways to do it. Consider creating user_interest table and doing it the right and simpler way. That is if your JSON actually looks like you have shown us and does not contain dynamic key-value pairs.