CODEWITHSUNDEEP
sqldatabasearrayspostgresql
niczak
niczak

Reputation: 3917

Find a string within an array column in PostgreSQL

I have built a series of views in a PostgreSQL database that includes a couple of array columns. 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 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.

Here is an example of data in the 'authors' array:

{"(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,,\"\")"}

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.

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.

Thanks in advance for any insight that can be provided on this topic.

Upvotes: 22

Views: 32262

Answers (2)

htaccess
htaccess

Reputation: 3130

The ANY() function can do the job for you:

SELECT * FROM people WHERE '123456789' = ANY(authors);

Given people.authors is of type text[].

Upvotes: 17

gsiems
gsiems

Reputation: 3790

Might this:

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

do the trick?

Otherwise, there is the unnest function...

The "Array Functions and Operators" chapter has more details.

Upvotes: 38

Related Questions