Find a row in an array column in PostgreSQL

Question

I built a series of views in a PostgreSQL database that includes a couple of columns of an array. The view definition is as follows:

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 iLike in the author column to determine if a specific user ID exists in this array. Obviously, I cannot use iLike in this data type, so I need to find a different approach.

Here is an example of the data in the author array:

{"(2373, t, e, f, \" 2011-08-01 11:57:40:69,696 \ "/ Pubs/pubs_edit_article.php, \" 2011-08-09 15:36:29,281833 \ " 000128343, A00592, Author, 1, Nicholas, K., Kreidberg, \ "\", 123456789, t, Administrator, A, A, A, 0, \ "\") "," (2374, t, e, e , \ "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, e, e, \ "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, e, e, \ "2011-08-09 15: 35: 11,845283 \" 567890123, \ "2011-08-09 15: 36 : 29.291388 \ "000128343, A00592, Author, 4, tests, T, Testerton, \" \ ", TestTesterton, F, N / A, A, A, A, \" \ ")"}

What I want to do is query in the view and find out if the string "123456789" exists in the array (that is, the user ID assigned to Nikolai Kreidberg in the array). I don’t care which user is assigned or where it appears in the array, all I need to know is if "123456789" is displayed anywhere in the array.

As soon as I know how to write a request that determines whether the above condition is true, my application will simply execute this request, and if the rows are returned, it will know that the user ID passed to the request is the author of this publication and act accordingly .

Thanks in advance for any information that may be provided on this topic.

Answer 1

Perhaps it:

 select ... from ... where ... and array_to_string(authors, ', ') like '%123456789%';` 

do the trick?

Otherwise, the unnest function unnest ...

The Array Functions and Operators chapter contains more information.