0

I have a table containing students details, and a column in it will be containing the favorite colors of students in json array

-----------------------------------------------------
id   name                              colors
-----------------------------------------------------
1 John                                {'red','blue'}
2 Cena                                {'red'}
3 Templeman                           {'orange'}
4 Kristy                              {'pink','red'}
------------------------------------------------------

now I want to list all the students who are having one of these favorite colors

select  all students  whose colors IN (red,pink)

so the list I expect is 

1 John                                {'red','blue'}
2 Cena                                {'red'}
4 Kristy                              {'pink','red'}

is there a way of making this? I tried searching individual color with (color=red OR color = pink) but that makes the query long and causes delay when the matching list is huge, I think IN like color IN (red,pink) would do better than that, is that possible to search on json array column?

CodeRows
  • 933
  • 1
  • 9
  • 15
  • Use `&&` operator. Like in this [question](http://stackoverflow.com/questions/1647385/postgres-comparing-two-arrays): `SELECT * FROM students where colors && '{"red"}' ;` – Ivan Burlutskiy Apr 20 '16 at 22:28
  • that didnt work, but a different one, just added my own answer – CodeRows Apr 21 '16 at 01:12
  • Oh, I see. Your `colors` described like text field. I tested query exactly on array: `CREATE TABLE students (id serial primary key, name varchar, color varchar[]); INSERT INTO students (name, colors) VALUES ('John', '{"red","blue"}'), ('Cena', '{"red"}'), ('Templeman', '{"orange"}'), ('Kristy', '{"pink", "red"}'); SELECT * FROM students where colors && '{"red"}' ; ` – Ivan Burlutskiy Apr 21 '16 at 06:13

1 Answers1

0

I found it finally

SELECT * FROM students where colors::jsonb ?| '{"red","pink"}' ;

so this fetches all records having any of the colors in their favorite column

?| is for any one matching element in the array, and ?& for all elements

CodeRows
  • 933
  • 1
  • 9
  • 15