Preparing where clause on JSON type columns in PostgreSQL

Question

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?

Use && operator. Like in this question: SELECT * FROM students where colors && '{"red"}' ; — Ivan Burlutskiy
– Ivan Burlutskiy, Commented Apr 20, 2016 at 22:28
that didnt work, but a different one, just added my own answer — CodeRows
– CodeRows, Commented Apr 21, 2016 at 1: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
– Ivan Burlutskiy, Commented Apr 21, 2016 at 6:13

CodeRows · Accepted Answer · 2016-04-21 01:15:18Z

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

answered Apr 21, 2016 at 1:15

CodeRows

1,0331 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Preparing where clause on JSON type columns in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related