I am doing the following query:
SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
AND NOT "blacklist" @> '{"example@gmail.com"}';
I expect this query to list all organizations that have gmail.com in the allowedDomains (string[]) and NOT to include the organization if that organization has in blacklist (string[]) example@gmail.com.
When I do:
SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
I get
And then I add the AND NOT "blacklist" @> '{"example@gmail.com"}';
And get 0 results.
(Just to clarify, I am just using example@gmail.com to redact the email, the query email I’m trying to filter is correctly typed)
>Solution :
If any of two arrays is null the result of the operator @> is also null. Use coalesce().
SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
AND NOT coalesce("blacklist", '{}') @> '{"example@gmail.com"}';
