PostgreSQL COALESCE with multiple jsonb_path_query_first returns null

Question

For example I have the next query:

SELECT COALESCE(
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$.a'),
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;

it return null although I use COALESCE.

How can I return in the case bb as the path $.a return null?

Thanks

Answer 1

The problem is, that a "JSON null" is not the same as a "SQL null". One option would be to get rid of all (JSON) null values first:

SELECT COALESCE(
   jsonb_path_query_first(jsonb_strip_nulls('{"a": null, "b": "bb"}'), '$.a'),
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;

Alternatively make the JSON path return a SQL null by using a condition:

SELECT COALESCE(
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$ ? (@.a != null).a'),
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;