为什么在postgreSQL中不能将NULL转换为JSON的null? [英] Why can't NULL be converted to JSON's null in postgreSQL?

问题描述

我可以表演

SELECT to_json(1)
SELECT to_json(1.4)
SELECT to_json('this is a nice json text')
SELECT to_json('{"become":"json"}')
SELECT to_json('null')

一切正常，但是当您执行以下操作时:

And it all works properly, however when you perform:

SELECT to_json(NULL::TEXT)

实际上，您获得了postgres内置的NULL，就像什么都没发生一样，当我期望与to_json('null')相同的结果作为示例SELECT to_json(someText)::TEXT FROM ...时，您可能会期望"input"，"stuff"，""和null，但是相反，您会得到"input"，"stuff"，""和

You, in fact get the postgres builtin NULL, like if nothing really happened, when I was expecting the same result as to_json('null') for exaple SELECT to_json(someText)::TEXT FROM ... maybe, you'd expect "input", "stuff", "" and null but instead you'd get "input", "stuff", "" and

我的问题是，为什么SELECT to_json(NULL::TEXT)不给您json空值，而是给您一个NULL指针?为什么在postgres中如此实现?一些具体原因?

My question is, why SELECT to_json(NULL::TEXT) doesn't give you a json null, but instead just a NULL pointer? why was it implemented like that in postgres? some specific reasons?

推荐答案

to_json被标记为STRICT函数，它的意思是-当任何参数为NULL时返回NULL.我不确定实施是否正确，也许是PostgreSQL错误.

to_json is marked as STRICT function, it is mean - returning NULL when any parameter is NULL. I am not sure if it is correct implementation, maybe it is PostgreSQL bug.

更新:在讨论Postgres邮件列表之后，这不是bug，而是功能-这种情况不是很简单，因此两种语言都支持NULL，但是NULL的行为与这些语言没有什么不同.很难决定是否必须立即将SQL NULL转换为JSON NULL并丢失SQL行为.如果需要不同的行为，则可以使用SQL函数:

Update: After discussion on Postgres' mailing list this is not the bug, but feature - the situation is not simple due fact, so both languages support NULL, but the behave of NULL is little bit different in any from these languages. It is hard to decide if SQL NULL have to be immediately transformed to JSON NULL and lost a SQL behave immediately. If you need different behave, you can use a SQL function:

CREATE OR REPLACE FUNCTION to_json2(anyelement)
RETURNS json AS $$
SELECT COALESCE(to_json($1), json 'null')
$$ LANGUAGE sql;

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