I have a company table with a jsonb column settings which stores, among other things, groups that are excluded in certain queries for that company.
I have a users table with column group
If I want to query users excluding those in groups specified in their company settings, my thought is that it would work like this:
SELECT users.*
FROM users
JOIN companies ON users.company_id = companies.id
WHERE users.group NOT IN (companies.settings ->> 'excluded')
While the query executes without error, it doesn't filter the results as exected. My thought is that using ->> causes the value to be read as text somehow, but using another operator causes the query to fail.
Is there a way to use dynamic data stored in an JSONB array in a WHERE NOT IN query as described above?