My table:
id | name | open_days
Column open_days is a json column with following structure:
{
daysOfWeek: ['mon','tue','sat']
months: ['may','november']
}
I would like to find rows where daysOfWeek contain searching element. Something like
SELECT * FROM places WHERE :day=ANY(open_days->'daysOfWeek')
But above query is not correct. Pleas help how to search is json array.
Thank you.
Assuming open_days is a jsonb column (which it should be), then you can use the contains operator ?:
select *
from places
where open_days -> 'daysOfWeek' ? :day
If it's a json column, you need to cast it open_days::jsonb -> ...
If you want to search for values that contain multiple weekdays, you can use the ?& operator:
select *
from places
where open_days -> 'daysOfWeek' ?& array['mon','tue']
the above would return all rows that contain mon and tue
One way is to use json_array_elements().
SELECT *
FROM places
WHERE :day = ANY (SELECT json_array_elements(open_days->'daysOfWeek')#>>'{}');
Yet another way to do this is with the containment operator.
select * from places where open_days @> '{"daysOfWeek":["mon"]}'
An advantage over using -> ... ? is that containment can use the default JSONB index, while the other would require a specialized index.
