Querying for null values inside a JSON column returns the wrong rows
WHERE data -> 'email' IS NULL returns fewer rows than expectedShort answer
A JSON field can be absent, hold the JSON value null, or sit in a row where the whole column is SQL NULL. PostgreSQL treats these differently. Use jsonb_typeof(col -> 'key') = 'null' for a JSON null, jsonb_typeof(...) IS NULL for a missing key, and col IS NULL for the whole column.
Fix it fast
1Find out which cause you have
Count how the key is stored across rows, using jsonb_typeof, which tells the three cases apart.
SELECT jsonb_typeof(data -> 'email') AS kind, count(*) FROM users GROUP BY 1;2Match what you see, then jump to its fix
- 1
kind shows the text null for the rows you meant to find, and IS NULL skips themThe key holds a JSON null, which is not SQL NULLFix - 2
kind is itself NULL (an empty cell) for those rowsThe key is missing, so the extraction operator returns SQL NULLFix - 3
WHERE data ->> 'email' = NULL returns nothing at allThe comparison is written with = NULLFix
- 1
What does the error look like?
WHERE data -> 'email' IS NULL returns fewer rows than expectedWHERE data ->> 'email' = NULL returns no rows
Cause 1 of 3
The key holds a JSON null, which is not SQL NULL
1Check
The PostgreSQL docs say a jsonb_typeof result of null must not be confused with an SQL NULL, and the data type page says SQL NULL is a different concept from JSON null. So data -> 'email' IS NULL is false for such rows.
SELECT count(*) FROM users WHERE jsonb_typeof(data -> 'email') = 'null';2Fix
Test for the JSON null with jsonb_typeof.
SELECT * FROM users WHERE jsonb_typeof(data -> 'email') = 'null';3Verify
The count matches the rows where kind is null in the first query.
SELECT count(*) FROM users WHERE jsonb_typeof(data -> 'email') = 'null';Confirmed by postgresql.org
Cause 2 of 3
The key is missing, so the extraction operator returns SQL NULL
1Check
The docs say the extraction operators return NULL, rather than failing, when no such key exists. jsonb_typeof of that NULL is also NULL.
SELECT count(*) FROM users WHERE jsonb_typeof(data -> 'email') IS NULL;2Fix
Use IS NULL on the jsonb_typeof result when you want rows with no such key (it also matches rows whose whole column is NULL, so add the next cause's test to separate them).
SELECT * FROM users WHERE data IS NOT NULL AND jsonb_typeof(data -> 'email') IS NULL;3Verify
Rows with a JSON null no longer appear in the result.
SELECT jsonb_typeof(data -> 'email'), count(*) FROM users WHERE data IS NOT NULL AND jsonb_typeof(data -> 'email') IS NULL GROUP BY 1;Confirmed by postgresql.org
Cause 3 of 3
The comparison is written with = NULL
1Check
The comparison page says ordinary comparison operators return null, not true, when an input is null, and tells you not to write expression = NULL.
SELECT 7 = NULL AS equals, 7 IS NULL AS is_null;2Fix
Use IS NULL or IS NOT DISTINCT FROM, and run the first query beforehand so you know whether the key is missing or holds a JSON null.
SELECT * FROM users WHERE data ->> 'email' IS NULL;
SELECT * FROM users WHERE data ->> 'email' IS NOT DISTINCT FROM NULL;3Verify
Both queries return the same rows, and the count is the one you expect from the first query.
SELECT count(*) FROM users WHERE data ->> 'email' IS NULL;Confirmed by postgresql.org
How do I stop it coming back?
- Pick one meaning for 'no email' in your JSON (missing key or JSON null) and write only that.
- Use jsonb_typeof in a CHECK or a test query to see which shape arrived from the API.
- Never write = NULL or <> NULL; use IS NULL and IS NOT NULL.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix