Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Querying for null values inside a JSON column returns the wrong rows

ErrorWHERE data -> 'email' IS NULL returns fewer rows than expected

Short 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.

  • Applies to PostgreSQL 17, jsonb
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1kind 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. 2kind is itself NULL (an empty cell) for those rowsThe key is missing, so the extraction operator returns SQL NULLFix
    3. 3WHERE data ->> 'email' = NULL returns nothing at allThe comparison is written with = NULLFix

What does the error look like?

  • WHERE data -> 'email' IS NULL returns fewer rows than expected
  • WHERE 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.