为什么在Postgres中JSON null不能转换为SQL null? [英] Why does JSON null not cast to SQL null in postgres?

问题描述

因此，下面的PostgreSQL代码段返回了null，它应该是这样:

So the following PostgreSQL snippet returns null, as it should:

select ('{"id": null}'::json->'id')

直观上，人们希望以下语句返回null或空字符串:

Intuitively, one would expect the following statement to return null or an empty string:

select ('{"id": null}'::json->'id')::TEXT

相反，它返回字符串"null".为什么?

Instead it returns the string "null". Why?

另外，

select ('{"id": null}'::json->'id')::INTEGER

返回cannot cast type json to integer和

select ('{"id": null}'::json->'id')::TEXT::INTEGER

返回invalid input syntax for integer: "null". (这里的用例是将INTEGER列中的JSON空转换为SQL空.)

returns invalid input syntax for integer: "null". (The use case here is casting a JSON null to a SQL null in an INTEGER column.)

有一个类似的问题，一个有点难以理解的答案似乎可以归结为"JSON空值和SQL空值略有不同"，并且没有进一步的解释.有人可以帮我了解这里发生了什么吗?这种明显的行为似乎很疯狂！

There's a similar question with a somewhat-unintelligible answer that seems to boil down to "JSON nulls and SQL nulls are slightly different" and no further explanation. Can someone help me understand what is going on here? This apparent behavior seems crazy!

一个人如何干净地解决这个问题?测试字符串"null"的代码臭味，并进行重构以在转换之前测试每个潜在节点的null/"null".还有其他想法吗?

How does one get around this cleanly? Testing for string "null" screams of code stink, and refactoring to test every single potential node for null/"null" before casting is equally yuck. Any other ideas?

推荐答案

使用->>运算符检索json字段.

Use the ->> operator for retrieving the json field.

这应该可以正常工作，并且为两个都正确返回null(如无值):

This should work and return null (as in, no value) correctly for both:

select ('{"id": null}'::json->>'id')::text
select ('{"id": null}'::json->>'id')::integer

我已经制造了一个小提琴来对其进行演示

PS:要获取字符串"null"，您需要将json定义为:{"id": "null"}

PS: to get the string "null", you'd need to define your json as: {"id": "null"}

这篇关于为什么在Postgres中JSON null不能转换为SQL null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！