I have table with jsonb
field - timetable
which has next structure:
{
1: [{start:0, finish: 12}],
2: [{start:0, finish: 12}, {start:12, finish:17}],
3: [{start:12, finish: 17}],
4: [{start:0, finish: 12}],
5: [{start:12, finish:17}, {start:17, finish: 0}],
6: [{start:0, finish: 12}, {start:12, finish:17}],
7: [{start:17, finish: 0}]
}
1...7 are days of week, which represent when user is available - morning/afternoon/evening. I'm doing filter where user could choose morning/afternoon/evening and days of week and get available users. If user checked all three time periods and all days I will have 21 variants. I wrote next query:
SELECT * FROM users WHERE
timetable <@ {1: [{start: 0, finish: 12}]} OR timetable <@ {1: [{start: 12, finish: 17}]} OR timetable <@ {1: [{start: 17, finish: 0}]} OR
timetable <@ {2: [{start: 0, finish: 12}]} OR timetable <@ {2: [{start: 12, finish: 17}]} OR timetable <@ {2: [{start: 17, finish: 0}]} OR
......
timetable <@ {7: [{start: 0, finish: 12}]} OR timetable <@ {7: [{start: 12, finish: 17}]} OR timetable <@ {7: [{start: 17, finish: 0}]}
Please advice me how to optimize my query. I know if user checked all possible periods and days I could just query with timetable is not null
. But I want to optimize my task in general.