jsonb_set_lax

Recently there were some complaints about the behaviour of the jsonb_set function. Specifically, the complain was that if the value argument of the function is null the result is null. This happens because the function is declared STRICT, like many PostgreSQL functions. STRICT is in fact another way of spelling RETURNS NULL ON NULL INPUT.

There are numerous advantages to having functions with this behaviour. It makes the code simpler and thus more efficient if it doesn’t have to worry about NULL arguments, and can just assume that they are not NULL.

This function has been in PostgreSQL with this behaviour since Release 9.5, so we’re not going to change it now.

Moreover, there wasn’t unanimity about what behaviour the people complaining would like to see instead. Some wanted an exception to be raised. Some wanted a JSON NULL to be used, which to me seems the most natural alternative. Some wanted the corresponding key to be deleted. And some just wanted the target to be returned unchanged.

Although we’re not going to change the existing function, we can provide a new function that implements these behaviours, and that’s what I’ve done. The function is called jsonb_set_lax, because it’s not strict, and it acts just like jsonb_set, except that it takes an extra argument that specifies how to treat the third argument if it’s NULL. The possible values for this are 'use_json_null' (the default), 'raise_exception', 'return_target' and 'delete_key'. This was committed the other day, so it will be in Release 13.

If you can’t wait that long, I have created an extension with the identical function, that can be build for releases 9.5, 9.6, 10, 11 and 12. It’s on github at https://github.com/adunstan/jsonb_set_lax