Why does extracting an explicit `null` in JSONB as text yield an SQL `null`?

Question

I am trying to understand handling nulls in PostgreSQL's jsonb type. Because of

# select 'null'::jsonb is null;
 ?column? 
----------
 f
(1 row)

I assume that they are different than SQL nulls (which makes sense) - according to the manual,

SQL NULL is a different concept.

Therefore, these two queries are not surprising at all:

# select '{"a": 1, "b": null}'::jsonb->'b' is null;
 ?column? 
----------
 f
(1 row)

# select '{"a": 1, "b": null}'::jsonb->'c' is null;
 ?column? 
----------
 t
(1 row)

per the manual:

The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such element exists.

Where the surprise begins, however, is this:

# select '{"a": 1, "b": null}'::jsonb->>'b' is null;
 ?column? 
----------
 t
(1 row)

# select '{"a": 1, "b": null}'::jsonb->>'c' is null;
 ?column? 
----------
 t
(1 row)

The latter one I can understand - we get an SQL null from the extraction, and casting a null to text leaves it as null - I assume ->> works that way, as the manual says

The field/element/path extraction operators return the same type as their left-hand input (either json or jsonb), except for those specified as returning text, which coerce the value to text.

(BTW, I could not find a confirmation that casting an SQL null to any other type yields null again in PostgreSQL - is it written somewhere explicitly?)

But the former is a mystery to me. The extraction should get me a jsonb null, and I thought casting to text should give me 'null' (i.e., a string saying "null"), just like

# select ('null'::jsonb)::text;
 text 
------
 null
(1 row)

But yet it returns a proper SQL null.

Why is that so?

Answer 1

To some extent this is a matter of opinion of the implementor; when converting between JSON data types and SQL data types, it is not always possible to find a perfect correspondence, particularly since SQL NULL is so weird.

But there is a certain logic to how it is implemented.

SELECT (JSONB '{"a": "null"}' -> 'a')::text,
       (JSONB '{"a": null}' -> 'a')::text;

  text  | text 
--------+------
 "null" | null
(1 row)

Casting to text always produces a result that, when cast back to the original type, produces the original value. That is a design principle in PostgreSQL.

So the JSON string "null" and the JSON null will be cast to different strings.

Now look at this:

SELECT JSONB '{"a": "null"}' ->> 'a',
       JSONB '{"a": null}' ->> 'a';

 ?column? | ?column? 
----------+----------
 null     | 
(1 row)

Here, different to the cast above, PostgreSQL tries to find the closest equivalent of the JSON value in SQL. You wouldn't want the string "null" to retain its double quotes, because that would be quite a different string in SQL, right?

But on the other hand it would also not feel right if "null" and null were represented the same way in SQL, would it?

As far as I know, JSON null means “not there”, and that is one of the meanings of SQL NULL. Also, having a JSON attribute with a value of null means about the same thing as omitting that attribute, doesn't it?

So while there is room for debate, I think there is some rhyme and reason behind the way it is implemented.