2021-09-13 A gotcha with PostgreSQL's jsonb type

As you may probably know, I am a big fan of PostgreSQL. That doesn’t mean that it’s always easy for me to use or intuitive, though. Today I ran into something strange (which is not that strange when you think of it, but still).

Consider a table with a jsonb field, which sometimes has some key and sometimes not (which is the main point of having a jsonb field after all):

# create temp table jsonb_gotcha (content jsonb);

# insert into jsonb_gotcha values ('{"key": "value"}'), ('{}');

# table jsonb_gotcha ;
 {"key": "value"}
(2 rows)

Now, if you say select * from jsonb_gotcha where content->>'key' = 'value';, you will get one row, which is what you certainly expect. However, you might expect this query:

select * from jsonb_gotcha where content->>'key' != 'value';

to select the other row (I certainly did!). Well, no – it selects nothing. When you think about it, it makes sense (kind of), since for that second row, content->>'key' is null, and hence is neither equal, nor not equal to anything. (Indeed, select * from jsonb_gotcha where content->>'key' is null; selects it all right.) For me, the confusion is here mainly because SQL null and JSON null have very different semantics (“unknown” vs. “nothing”). Well, at least now I know about this behavior…

CategoryEnglish, CategoryBlog, CategoryPostgreSQL