How to use an array of strings stored in JSONB column in a NOT IN () query

Viewed 14

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?

0 Answers
Related