Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find a string within an array column in PostgreSQL

Tags:

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.

like image 943
niczak Avatar asked Sep 03 '11 00:09

niczak


People also ask

Which of the following will be used to search data in the array in PostgreSQL?

The elements of the array can be retrieved using the SELECT statement. The values of the array column can be enclosed within square brackets [] or curly braces {}. We can search for array column values using the ANY() function.

How do you select a value from an array in SQL?

SELECT (array['one','two','three'])[state] FROM mytable WHERE id = 1; But as already stated, the CASE statement is the standard and portable method.

How do I query an array of columns in SQL?

Step 1: Group the data by the field you want to check. Step 2: Left join the list of required values with the records obtained in the previous step. Step 3: Now we have a list with required values and corresponding values from the table.


2 Answers

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.

like image 152
gsiems Avatar answered Sep 27 '22 19:09

gsiems


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

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

Given people.authors is of type text[].

like image 28
htaccess Avatar answered Sep 27 '22 21:09

htaccess