英文:
PostgreSQL COALESCE with multiple jsonb_path_query_first returns null
问题
You can achieve the desired result by using the COALESCE function along with the jsonb_path_query_first function in PostgreSQL. Here's the translated code:
例如，我有以下查询：
SELECT COALESCE(
jsonb_path_query_first('{"a": null, "b": "bb"}', '$.a'),
jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;
它会返回 null，尽管我使用了 COALESCE。如何在路径 $.a 返回 null 的情况下返回 bb？
谢谢。
英文:
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
答案1
得分: 2
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:
问题是，"JSON null" 和 "SQL null" 不同。一种解决方法是首先消除所有（JSON）null值：
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:
或者，通过使用条件使JSON路径返回SQL null：
SELECT COALESCE(
jsonb_path_query_first('{"a": null, "b": "bb"}', '$ ? (@.a != null).a'),
jsonb_path_query_first('{"a": null, "b": "bb"}', '$.b')
) AS value;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论