How to check JSONB has a field in root?

Question

I try this:

select * from "User" where "partnerData" -> 'name' != NULL

partnerData is a JSONB. I would see those rows, does not have the name field in JSON.

Answer 1

You can't use <> (or != or any other operator) to check for NULL values, you need to use IS NULL. Using -> also returns a jsonb value which might be the literal null not the SQL NULL value. So you should use ->> to return a text value (which would then be a SQL NULL)

select * 
from "User" 
where "partnerData" ->> 'name' IS NULL

Note that this doesn't distinguish between a JSON value that contains the key name but with a value of NULL and a JSON value that does not contain the key at all.

If you only want to check if the key exists (regardless of the value - even if it's a JSON null), use the ? operator.

where "partnerData" ? 'name'