postgresql 获取json列键为空的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23911740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get records where json column key is null
提问by morgoth
I have table userswith json column details.
我有users带有 json 列的表details。
I want to fetch all user records where details["email"] is null or email key doesn't exist.
我想获取 details["email"] 为空或电子邮件密钥不存在的所有用户记录。
This doesn't work:
这不起作用：
SELECT users.* FROM users where details->'email' IS NOT NULL;
SELECT users.* FROM users where details->'email' IS NOT NULL;
ERROR: operator does not exist: json -> boolean
ERROR: operator does not exist: json -> boolean
回答by Roman Pekar
use brackets (). Looks like compiler tries to see it like details->('email' IS NOT NULL). So you can fix it like this:
使用括号()。看起来编译器试图将其视为details->('email' IS NOT NULL). 所以你可以像这样修复它：
select *
from users
where (details->'email') is not null
actually, to get records where details["email"] is null or email key doesn't exist, you can use this query:
实际上，要获取details["email"] 为空或电子邮件键不存在的记录，您可以使用以下查询：
select *
from users
where (details->>'email') is null
as described in this answer.
如本答案所述。
回答by pozs
You need to use the ->>operator for that:
您需要为此使用->>运算符：
select *
from users
where (details->>'email') is not null
Because the ->operator returns 'null'::json(and not sql NULL) if the key is exists but with a json null value.
因为如果键存在但具有 json 空值，则->运算符返回'null'::json（而不是 sql NULL）。
