PostgreSQL `null` value in JSON column not filtered by `IS NOT NULL` as expected

Question

(PostgreSQL version: 15.10)

I am trying to query a table with a nullable json-type column. Here is the simplified table description:

db=> \d tablename
                          Table "public.tablename"
   Column           |            Type             | Collation | Nullable |   
--------------------+-----------------------------+-----------+----------
 id                 | uuid                        |           | not null |
 group_id           | uuid                        |           |          |
 field_id           | uuid                        |           |          |
 mapping_key        | character varying           |           |          |
 sub_field_mappings | json                        |           |          |
 option_mappings    | json                        |           |          |
 time_created       | timestamp without time zone |           |          |
 time_updated       | timestamp without time zone |           |          |

Here is the query I was trying to run:

db=>
SELECT id, option_mappings from tablename
where option_mappings IS NOT NULL limit 10;

    id    |     option_mappings
----------+-------------------------
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | null
 <UUID> | [Actual JSON data here]

I'm confused: why am I getting rows with null values when I specifically queried with IS NOT NULL? The same behavior happened with the sub_field_mappings column. As a sanity check, I also did this:

db=> SELECT id, option_mappings from tablename where option_mappings IS NULL limit 10;

and it returned nothing. Okay, so the null I am seeing is not a NULL value?

I ran this:

db=> SELECT json_typeof(option_mappings) from tablename where option_mappings IS NOT NULL limit 10;
 json_typeof 
-------------
 null
 null
 null
 null
 null
 null
 null
 null
 null
 array
(10 rows)

At this point, I was losing my mind. HOW CAN A VALUE WITH TYPE 'null' NOT BE NULL?

Update

I still have no idea why my query was not filtering rows with null values as expected, but this query did what I wanted:

db=> SELECT id, option_mappings from tablename where json_typeof(option_mappings) != 'null' limit 10;

I still have no idea why a null value in a JSON field did not equate to NULL in a WHERE clause. If anyone has any insights here, that would be appreciated!

psql (the command line tool) tip: use the metacommand \pset null ∅ to assign a special string of your own choice to display the SQL NULL (the default is an empty string) to distinguish them more clearly. — Daniel Vérité
– Daniel Vérité, Commented Jun 27 at 18:16
The issue is you did not read the docs JSON TYPES: Table 8.23. JSON Primitive Types and Corresponding PostgreSQL Types where the first value is the JSON type and the second is the mapped Postgres type. So null (none) SQL NULL is a different concept — Adrian Klaver
– Adrian Klaver, Commented Jun 27 at 18:42

Daniel Vérité · Accepted Answer · 2025-06-27 18:08:11Z

1

Okay, so the null I am seeing is not a NULL value?

That's correct.

There are the SQL NULL and the JSON null. They are completely different.

Here is a JSON null:

=> select 'null'::json;
 json 
------
 null

=> select 'null'::json is null;
 ?column? 
----------
 f

Here is a SQL NULL:

=> select null::json
 json 
------
 
=> select null::json is null;
 ?column? 
----------
 t

answered Jun 27 at 18:08

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL `null` value in JSON column not filtered by `IS NOT NULL` as expected

Update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related