PostgreSQL中COALESCE多jsonb_path_query_first返回null问题

为什么原查询的COALESCE不生效？ #

jsonb_path_query_first在路径存在但对应JSON值为null时，返回的是JSONB类型的null，而非SQL标准的NULL。COALESCE仅在参数为SQL NULL时才会尝试下一个参数，因此它会把JSONB的null当作有效取值，不会触发第二个分支。

解决方法 #

方法1：用->>操作符转换为文本（适合需要字符串结果的场景）

->>会将JSON值转为文本，当JSON值是null时，返回SQL的NULL，这样COALESCE就能正常生效：

SELECT COALESCE(
   '{"a": null, "b": "bb"}'::jsonb ->> 'a',
   '{"a": null, "b": "bb"}'::jsonb ->> 'b'
) AS value;

方法2：判断JSON值类型（适合保留JSONB类型的场景）

通过jsonb_typeof检查结果类型，将JSONB的null转为SQL NULL：

SELECT COALESCE(
   CASE 
      WHEN jsonb_typeof(jsonb_path_query_first('{"a": null, "b": "bb"}', '$.a')) <> 'null' 
      THEN jsonb_path_query_first('{"a": null, "b": "bb"}', '$.a') 
   END,
   jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;

方法3：在JSON路径中过滤null值

修改路径表达式，只返回非null的结果，这样当$.a是null时，jsonb_path_query_first会返回SQL NULL：

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

内容的提问来源于stack exchange，提问作者Shay Zambrovski