Why can't NULL be converted to JSON's null in postgreSQL?

Question

I can perform

SELECT to_json(1)
SELECT to_json(1.4)
SELECT to_json('this is a nice json text')
SELECT to_json('{"become":"json"}')
SELECT to_json('null')

And it all works properly, however when you perform:

SELECT to_json(NULL::TEXT)

You, in fact get the postgres builtin NULL, like if nothing really happened, when I was expecting the same result as to_json('null') for exaple SELECT to_json(someText)::TEXT FROM ... maybe, you'd expect "input", "stuff", "" and null but instead you'd get "input", "stuff", "" and

My question is, why SELECT to_json(NULL::TEXT) doesn't give you a json null, but instead just a NULL pointer? why was it implemented like that in postgres? some specific reasons?

Answer 1

The to_json function is marked as STRICT, which means, return NULL when any parameter is NULL. I'm not sure if this is intended, maybe it's a PostgreSQL bug.

Update: After discussing this on Postgres' mailing list, this is not a bug, but a feature - the situation is not as simple due to the fact that both languages support NULL, but the behavior of NULL is little bit different between these two languages. It's hard to decide whether an SQL NULL should be immediately transformed to a JSON NULL and lose its SQL behavior immediately. If you need different behavior, you can use an SQL function:

CREATE OR REPLACE FUNCTION to_json2(anyelement)
RETURNS json AS $$
SELECT COALESCE(to_json($1), json 'null')
$$ LANGUAGE sql;