query date range in MongoDB from nodejs

Question

I am trying to get all the users within the specified date range, My database structure looks like this:

{
    _id : ObjectId("58b545d4b57eeb2cc456248a"),
    "attendance" : [ 
        {
            "2017-03-16" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-17" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-18" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-19" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-20" : ["58c108b9ebd9ae24dca81634","58c108b9ebd9ae24dca81635"]
        }, 
        {
            "2017-03-17" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-18" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-19" : ["58c108b9ebd9ae24dca81634"],
            "2017-03-20" : ["58c108b9ebd9ae24dca81634","58c108b9ebd9ae24dca81635"]
        }
    ]
    .......
}

and to query it:

routes.find({_id: ObjectId("58b545d4b57eeb2cc456248a"), $or:[{'attendance[0]' : {$gte: '2017-03-17' }}, {'attendance[1]': {$gte: '2017-03-17'}}]})

but it is not fetching any results.

Vince Bowdren · Accepted Answer · 2017-03-20 10:37:29Z

3

It looks like your dates are being held in the keys (fieldnames); but MongoDB is only designed to query on data being held in the field values. I suggest you restructure your data to something like this:

{
    _id : ObjectId("58b545d4b57eeb2cc456248a"),
    "attendance" : [ 
        {
            "dateitems" : [
                {"when" : ISODate("2017-03-16"), "who" : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-17"), "who" : : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-18"), "who" : : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-19"), "who" : : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-20"), "who" : : [ObjectId("58c108b9ebd9ae24dca81634"),ObjectId("58c108b9ebd9ae24dca81635")]}
            ]
        }, 
        {
            "dateitems" : [
                {"when" : ISODate("2017-03-17"), "who" : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-18"), "who" : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-19"), "who" : [ObjectId("58c108b9ebd9ae24dca81634")]},
                {"when" : ISODate("2017-03-20"), "who" : [ObjectId("58c108b9ebd9ae24dca81634",ObjectId("58c108b9ebd9ae24dca81635")]}
            }
        }
    ]
    .......
}

With this kind of data structure, you can query to get the output you need, for example like this:

routes.aggregate([
    {$match: { _id: ObjectId("58b545d4b57eeb2cc456248a")}},
    {$match: {"attendance.dateitems.when" : {$gt: ISODate("2017-03-17")}}}
})

Here are some of the key points I've changed in my restructuring:

Instead of strings, every ObjectId reference is in a true ObjectID type; this will allow effective comparisons and lookups.
Instead of strings, every date is in a standardised Date data type using the ISODate format; this will allow effective comparisons such as $gt.
The attendance data has been expanded into an array of dateitem objects, each of which has a field for the date and a field for the referenced objects (I'm guessing those are the ids of people attending); that way, each data value is stored as a field value - not as a fieldname.

answered Mar 20, 2017 at 10:37

Vince Bowdren

9,2853 gold badges37 silver badges61 bronze badges

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

4 Comments

warl0ck Over a year ago

thanks for the valuable input, can you please help me out how am I supposed to edit the current database and save it in the new format, since am new to this

Vince Bowdren Over a year ago

@warl0ck That's a big question, and not possible to answer reliably without knowing how your database is created and maintained. If it is under the control of a single application, then one sensible approach might be to define a data migration process to transform the existing data into the new format. However, you will also need to alter your application's database commands, so as to store new data in the new format.

warl0ck Over a year ago

Yes, I know i will have to change the data format of how it is saved and the model as well, in the nodejs template, but how to change so that i can search date range. PS: I have all the access.

Mirko Over a year ago

From nodejs I get ReferenceError: ISODate is not defined

Rohit Parte · Accepted Answer · 2019-04-20 15:56:51Z

0

In node.js we can query ISODate by new Date() to find date range data

db.collectionName.find({ 
   'updatedAt': {
          $gte: new Date('2019-04-19T14:16:04.065Z'),
          $lte: new Date('2019-04-19T14:16:04.070Z')
        }})

answered Apr 20, 2019 at 15:56

Rohit Parte

4,24233 silver badges31 bronze badges

Collectives™ on Stack Overflow

query date range in MongoDB from nodejs

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related