在 PostgreSQL 的数组列中查找字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7290373/
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
Find a string within an array column in PostgreSQL
提问by Nicholas Kreidberg
I have built a series of views in a PostgreSQL database that includes a couple of array columns. The view definition is as follows:
我在包含几个数组列的 PostgreSQL 数据库中构建了一系列视图。视图定义如下：
create view articles_view as
(select articles.*,
array(select row(people.*)::people
from people
where articles.spubid=people.spubid and
people.stype='Author' and
bactive='t'
order by people.iorder) as authors,
array(select row(people.*)::people
from people
where articles.spubid=people.spubid and
people.stype='Editor' and
bactive='t'
order by people.iorder) as editors,
array(select row(people.*)::people
from people
where articles.spubid=people.spubid and
people.stype='Reviewer' and
bactive='t'
order by people.iorder) as reviewers,
array(select row(status.*)::status
from status
where articles.spubid=status.spubid and
bactive='t') as status
from articles
where articles.bactive='t');
Essentially what I want to do is an iLike on the 'author' column to determine if a specific user id exists in that array. Obviously I can't use iLike on that datatype so I need to find another approach.
本质上，我想要做的是在“作者”列上添加一个 iLike，以确定该数组中是否存在特定的用户 ID。显然我不能在该数据类型上使用 iLike，所以我需要找到另一种方法。
Here is an example of data in the 'authors' array:
以下是“authors”数组中的数据示例：
{"(2373,t,f,f,\"2011-08-01 11:57:40.696496\",/Pubs/pubs_edit_article.php,\"2011-08-09 15:36:29.281833\",000128343,A00592,Author,1,Nicholas,K.,Kreidberg,\"\",123456789,t,Admin,A,A,A,0,\"\")","(2374,t,f,f,\"2011-08-01 11:57:40.706617\",/Pubs/pubs_edit_article.php,\"2011-08-09 15:36:29.285428\",000128343,A00592,Author,2,John,D.,Doe,\"\",234567890,t,IT,A,A,A,0,\"\")","(2381,t,f,f,\"2011-08-09 14:45:14.870418\",000128343,\"2011-08-09 15:36:29.28854\",000128343,A00592,Author,3,Jane,E,Doe,\"\",345678901,t,Admin,A,A,A,,\"\")","(2383,t,f,f,\"2011-08-09 15:35:11.845283\",567890123,\"2011-08-09 15:36:29.291388\",000128343,A00592,Author,4,Test,T,Testerton,\"\",TestTesterton,f,N/A,A,A,A,,\"\")"}
{"(2373,t,f,f,\"2011-08-01 11:57:40.696496\",/Pubs/pubs_edit_article.php,\"2011-08-09 15:36:29.281833\",00012833 A00592,Author,1,Nicholas,K.,Kreidberg,\"\",123456789,t,Admin,A,A,A,0,\"\")","(2374,t,f,f,\ "2011-08-01 11:57:40.706617\",/Pubs/pubs_edit_article.php,\"2011-08-09 15:36:29.285428\",000128343,A00592,D.,2,Do ,\"\",234567890,t,IT,A,A,A,0,\"\")","(2381,t,f,f,\"2011-08-09 14:45:14.870418\ ",000128343,\"2011-08-09 15:36:29.28854\",000128343,A00592,Author,3,Jane,E,Doe,\"\",345678901,t,Admin,A,A,A, ,\"\")","(2383,t,f,f,\"2011-08-09 15:35:11.845283\",567890123,\"2011-08-09 15:36:29.291388\", 000128343,A00592,Author,4,Test,T,Testerton,\"\",TestTesterton,f,N/A,A,A,A,,\"\")"}
What I want to be able to do is a query the view and find out if the string '123456789' (that is the user id assigned to Nicholas Kreidberg in the array) exists in the array. I don't care which user it is assigned to or where it appears in the array, all I need to know is if '123456789' shows up anywhere in the array.
我希望能够做的是查询视图并找出字符串“123456789”（即分配给数组中 Nicholas Kreidberg 的用户 ID）是否存在于数组中。我不在乎它被分配给哪个用户或它出现在数组中的哪个位置，我需要知道的是“123456789”是否出现在数组中的任何位置。
Once I know how to write a query that determines if the condition above is true then my application will simply execute that query and if rows are returned it will know that the user id passed to the query is an author for that publication and proceed accordingly.
一旦我知道如何编写确定上述条件是否为真的查询，那么我的应用程序将简单地执行该查询，如果返回行，它将知道传递给查询的用户 ID 是该出版物的作者并相应地进行。
Thanks in advance for any insight that can be provided on this topic.
在此先感谢您提供有关此主题的任何见解。
回答by gsiems
Might this:
可能这个：
select ...
from ...
where ...
and array_to_string(authors, ', ') like '%123456789%';`
do the trick?
做的伎俩？
Otherwise, there is the unnestfunction...
否则，有unnest功能...
The "Array Functions and Operators" chapter has more details.
“数组函数和运算符”一章有更多细节。
