Postgres从数组有元素的表中选择*

I am using Postgres 9.3.2 to make a database of contacts.

我正在使用Postgres 9.3.2建立一个联系人数据库。

Example: If i have a row in my table that looks something like this.

例如:如果我的表中有一行像这样。

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

PS: firstName, lastName and emails are columns in my db and the value associated is the value for that column for that specific row.

注:firstName、lastName和电子邮件是我的db中的列，关联的值是该列对应的特定行的值。

I want to be able to query the db so that if i query for the email "email@four.com" the result is nothing but if i query for "email@two.com" the result will be the above row entry.

我希望能够查询db，以便查询邮件“email@four.com”，结果就是如果我查询“email@two.com”，结果将是上面的行条目。

I dont think the query

我不认为这个问题。

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

"在contactTable中选择*，其中电子邮件="email@two.com"

will work. instead i want to do something like

将工作。相反，我想做一些类似的事情

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

any ideas on how to do this?

有什么办法吗?

2 个解决方案

#1

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

“从contactTable中选择*，其中电子邮件包含“email@two.com”。

I think you want:

我认为你想要的:

"Select * from contactTable where thejsonfield -> emails

“从contactTable中选择*，其中包含jsonfield ->电子邮件。

Example setup, after fixing up your totally broken 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;

The following will work in PostgreSQL 9.4, but unfortunately does not in 9.3 due to the oversight of the missing json_array_elements_text function:

以下将在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';

For 9.3, you have to use a clumsier method to scan the json array for matching values:

对于9.3，您必须使用一个clusier方法来扫描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';

You can't use the simple IN or = ANY constructs because (at this point) PostgreSQL doesn't understand that you might have a json array, so it'll fail with:

您不能使用or = 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...
                                                     ^

as it expects a PostgreSQL array, not a json array, and there's no convenient builtin to turn a json array into a PostgreSQL array yet.

因为它期望的是PostgreSQL数组，而不是json数组，而且还没有方便的内置程序将json数组转换为PostgreSQL数组。

#2

Postgres has support for parsing JSON. Here is documentation: http://www.postgresql.org/docs/9.3/static/functions-json.html. I can't give you more detailed answer since you didn't provide exact data and schema, but it's easy to find the right function in documentation.

Postgres支持解析JSON。这是文档:http://www.postgresql.org/docs/9.3/static/functions-json.html。由于您没有提供准确的数据和模式，所以我无法提供更详细的答案，但是在文档中很容易找到正确的函数。

#1