logo标签列表

Postgres查询语句：SELECT * FROM Table WHERE数组包含元素。

sqlarraysjsonpsql
3
我将使用Postgres 9.3.2创建一个联系人数据库。
示例：如果我的表中有一行看起来像这样。
{
    firstName : "First name"
    lastName : "Last name"
    emails : ["email@one.com", "email@two.com", "email@three.com]
}

注意：firstName、lastName和emails是我数据库中的列，与该特定行相关联的值是该列的值。

我希望能够查询数据库，以便如果我查询电子邮件“email@four.com”，则结果为空，但如果我查询“email@two.com”，则结果将是上面的行条目。

我认为查询

"Select * from contactTable where emails="email@two.com""

不会起作用。相反，我想做类似于

"Select * from contactTable where emails contains "email@two.com""

有什么想法可以实现这个吗？
3
你能将数据库规范化，使得电子邮件和电话号码成为个别字段吗？这样会简单得多。 - n8wrl
@n8wrl 我认为这是正确的方法，但很多时候由于许多原因（非技术原因）我们无法这样做。=) - Andre Figueiredo
1
@AndréFigueiredo：明白了。这让我想起了以前一个项目中我们将XML存储为二进制大对象的糟糕经历。无法搜索。规范化才是解决之道。 - n8wrl
2个回答
3

"选择* from contactTable where emails 包含 "email@two.com""

我认为你想要：

"选择* from contactTable where thejsonfield -> emails

示例设置，在修复您完全损坏的json之后：

CREATE TABLE contacts AS SELECT '{
    "firstName" : "First name",
    "lastName" : "Last name",
    "emails" : ["email@one.com", "email@two.com", "email@three.com"]
}'::json AS myjsonfield;

以下内容适用于PostgreSQL 9.4，但很遗憾在9.3中由于缺少json_array_elements_text函数而无法使用：
select * 
from contacts, 
lateral json_array_elements_text(myjsonfield -> 'emails') email
where email = 'email@two.com';

对于9.3版本，您需要使用一种更加繁琐的方法来扫描JSON数组以查找匹配的值：

select * 
from contacts, 
lateral json_array_length(myjsonfield -> 'emails') numemails, 
lateral generate_series(0, numemails) n 
WHERE json_array_element_text(myjsonfield -> 'emails', n) = 'email@two.com';

您不能使用简单的IN或= ANY语句，因为（此时）PostgreSQL不理解您可能有一个JSON数组，所以会出现以下错误：

regress=> SELECT * FROM contacts WHERE 'email@two.com' = ANY (myjsonfield->'emails');
ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT * FROM contacts WHERE 'email@two.com' = ANY (myjsonfi...
                                                     ^

因为它期望的是一个PostgreSQL数组，而不是一个json数组，并且还没有方便的内置函数可以将json数组转换成PostgreSQL数组。

1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接