Search code examples
postgresqlpostgresql-14

PostgreSQL COALESCE with multiple jsonb_path_query_first returns null

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

Solution

  • 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;