sql - Postgres SELECT * FROM 表中数组有元素

Question

我正在使用 Postgres 9.3.2 创建一个包含联系人 的数据库。

示例:如果我的表中有一行看起来像这样。

{
    firstName : "First name"
    lastName : "Last name"
    emails : ["email@one.com", "email@two.com", "email@three.com]
}

PS:名字、姓氏和电子邮件是我的数据库中的列，关联的值是该特定行的该列的值。

我希望能够查询数据库，这样如果我查询电子邮件“email@four.com”，结果什么也没有，但是如果我查询“email@two.com”，结果将是上面的行条目。

我不认为查询

“选择 * from contactTable where emails="email@two.com""

会起作用。相反，我想做类似的事情

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

关于如何做到这一点有什么想法吗？

Answer 1

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

我想你想要:

“从 contactTable 中的 json 字段中选择 * -> 电子邮件

设置示例，在修复完全损坏的 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 中工作，但不幸的是，由于缺少 json_array_elements_text 函数的疏忽，在 9.3 中不起作用:

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 数组。