need a better schema to query the schedule in mongodb

Question

I need to design a schedule schema in mongodb, the original data format is something like this {"schedule_begin":Date,"schedule_end":Date,"schedule_days_runs":"1111100"}

Note in the schema there is a property schedule_days_runs with data "schedule_days_runs":"1111100". It is 7 days in the week, where '1' represents working and '0' represents not working.

Basically, what application does is to find all the schedules with working in certain date (e.x Monday), that means we need to query the schedule , schedule_start =< one_day <= schedule_end, also ensure the queried schedules are also working on Monday.

A easy solution is to store "1111100" directly into the mongodb in String, only use schedule_start and schedule_end for query, and then use the application logic to get the results. However this solution could be not handy, since usually I would get thousands of queries, and half of the queries are not the answer. For example, to get the working schedules on a specific day '1111100'(the schedule works from Monday to Friday), the results of the query gives me many useless things like '0000011' (the schedule works in weekend).

I am trying to figure out a better solution, can somebody has a better idea?

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

I'm still doing my first baby steps with mongodb, but I think using an array with different values would suit mongodb better. Something along these lines:

{ 
  "schedule_begin": Date,
  "schedule_end":Date,
  "schedule_days_runs": ["mo", "tu", "we", "th", "fr", "sa", "su"]
}

Then you could use queries like:

{ schedule_days_runs: "mo" }

to get all that run on Mondays.

As I still learn there might be some typos in there. Also, from what I see, with mongodb, we have to get away from almost all we learned using relational DBs, this is something else. Using a clean slate approach made me get though some issues which were difficult to get by using a relational approach.

I used this question as reference: Querying array elements with Mongo

To find out the length of the array, this question is of use: In Mongo DB, how do I find documents where array size is greater than 1

Quote:

using $where

db.accommodations.find( { $where: "this.name.length > 1" } );

So based on this, you should try:

{ $where : "this.schedule_days_runs.length > 1" }

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 05 '13 at 09:34

ppeterka

20,583
6
63
78

so now I use "schedule_days_runs": [1,2,3,4,5,6,7], but how can I query documents where array size is greater than 1? I have tried this, {"schedule_start_date": {"$lte": today},"schedule_end_date":{"$gte":today}, {"$where": {this.schedule_days_runs.length:{"$gt":0}}} }, but it gives me a error. It occurs on the code {"$where": {this.schedule_days_runs.length:{"$gt":0}}}, how should I do? – user824624 Mar 05 '13 at 11:11
@user824624 I updated the answer, I found an answer to this question earlier today: `{$where: "this.schedule_days_runs.length > 1" }` – ppeterka Mar 05 '13 at 11:16
I have tried this "$where": {"this.schedule_days_runs.length" > 1}, it gives me a error of SyntaxError: Unexpected token > – user824624 Mar 05 '13 at 12:51
Make sure you put the double quotes after 1 so exactly as @ppeterka showed `{$where: "this.schedule_days_runs.length > 1" }` – br3w5 Mar 05 '13 at 20:40
I still think the aggregation framework is a good option for you so I've expanded my answer below – br3w5 Mar 05 '13 at 21:44

br3w5 · Answer 2 · 2013-03-13T16:03:18.667

I'm not sure how your application is adding elements to the array but one option is to use $push or $addToSet to add days to the array only when someone is actually working. You can then check the length of the array to know how many days each person has worked.

You can also use the aggregation framework (I think this is the better option) to run queries and produce reports. For example, using the following document structure:

{ 
  "userId":"user ID"
  "schedule_begin": "Date",
  "schedule_end": "Date",
  "schedule_days_runs": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]
}

You could run the following query to find out who has worked on a Monday.

db.schedule.aggregate([{$unwind:"$schedule_days_runs"},{$group:{"_id":{"working_days":"$schedule_days_runs", "name":"$userId"}}},{$match:{"_id.working_days":"Monday"}},{$sort:{"_id.name":1}}])

This will create separate documents from each element of the array using $unwind and $group them by userId and days worked, then $match with only those people who worked on a Monday. Note the dot notation to reach into embedded documents. The aggregation framework also allows you to redefine fields e.g."name":"$userId". An example result from this query is:

{
    "result" : [
            {
                    "_id" : {
                            "working_days" : "Monday",
                            "name" : "Charlie Sheen"
                    }
            },
            {
                    "_id" : {
                            "working_days" : "Monday",
                            "name" : "Donald Duck"
                    }
            },
            {
                    "_id" : {
                            "working_days" : "Monday",
                            "name" : "Superman"
                    }
            }
    ],
    "ok" : 1

}

You can do more with the aggregation framework and each part of the query pipes information to the next. So above, $unwind pipes the info through to $group that then matches the document. You can keep going - check out the docs for more detailed info.

need a better schema to query the schedule in mongodb

2 Answers2