I have a posts table with a JSON column of an array with IDs. This column's data looks exactly like:
[1, 3, 17, 19] These values are not quoted.
To check if a user should see a post, I simply use JSON_CONTAINS(userlist, '(the user ID)', '$'). However, after many thousands of posts, this is starting to get too slow. I'd like to avoid normalizing this to another table with proper relations for now, so I'm wondering what's the best way to see if a user ID exists in a field like what I have.
Note: this is not exactly a duplicate. My values are straight integers, which is why I seemingly can't use JSON_SEARCH()?
You really should bite the bullet and normalise, as this operation is only going to get slower. In the meantime, there are a couple of ways you can do this with string operations using LIKE and REGEXP:
select userlist regexp '\\b3\\b' AS got_3,
userlist regexp '\\b7\\b' AS got_7
from test
;
select userlist like '[3,%' or userlist like '% 3,%' or userlist like '%,3]' AS got_3,
userlist like '[7,%' or userlist like '% 7,%' or userlist like '%,7]' AS got_7
from test
In both cases for your sample data the output is:
got_3 got_7
1 0
Using LIKE will probably be faster than JSON_CONTAINS, but using REGEXP probably won't. You'd need to benchmark on your server.
If you're using MySQL 8+, then you can use JSON_TABLE:
select *
from test
join json_table(userlist,
'$[*]' columns (user int path '$')
) ul
where ul.user = 3
Again, performance will be dependent on your server.