The posgres filter "not contains" doesn't work as I expect-CodePudding

I am doing the following query:

SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'
AND NOT "blacklist" @> '{"[email protected]"}';

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[]) [email protected].

When I do:

SELECT *
FROM "Organization"
WHERE "allowedDomains" @> '{"gmail.com"}'

I get

And then I add the AND NOT "blacklist" @> '{"[email protected]"}';

And get 0 results.

(Just to clarify, I am just using [email protected] to redact the email, the query email I'm trying to filter is correctly typed)

CodePudding user response：

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", '{}') @> '{"[email protected]"}';