CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Train Heartnet
Train Heartnet

Reputation: 815

How to filter by key, find by date range, and then aggregate by week in mongodb?

I have a mongo collection named MyCollection which consists of documents that look like this:

{ 
    "_id" : ObjectId("58085384e4b0f70605461e3f"), 
    "uid" : "fa1aeafc-18db-41a5-8ee5-ac0c32428fe1",
    "Key1" : "Value1"  
    "timeStamp" : ISODate("2016-08-23T17:58:20.000+0000"), 
}

Some of the documents have Key1, while others have Key2 or Key3.

Now, I wish to do the following:-

  1. Get only those documents that have Key1 or Key2.
  2. From the resulting set of documents above, get only those documents which are in the timeStamp range Date(2016, 08, 01) to Date(2016, 08, 31).
  3. For the resulting set of documents above, aggregate them by week.

How do I write a mongo query for this?

Upvotes: 2

Views: 3295

Answers (2)

chridam
chridam

Reputation: 103425

Using the aggregation framework, the above steps can be done with the $match and $group pipeline steps as follows:

var start = new Date(2016, 7, 1), // months are zero-based indexes i.e Aug month index is 7
    end = new Date(2016, 7, 30); // August month ends on the 30th

db.collection.aggregate([
    { /* filter steps 1 & 2 */
        "$match": {
            "$or": [
                { "Key1": { "$exists": true } },
                { "Key2": { "$exists": true } }
            ],
            "timeStamp": { "$gte": start, "$lte": end }
        }
    },
    { /* group by week */ 
        "$group": {
            "_id": { "$week": "$timeStamp" },
            "count": { "$sum": 1 },
            "uids": { "$push": "$uid" }
        }
    }
])

In the above, the date range is created using the Date() constructor where months are zero-based indexes. The $exists operator is used to determine the existence of a key and use the $week date aggregate operator to get the week number in a date between 0 (the partial week that precedes the first Sunday of the year) and 53 (leap year).

For week numbers starting at 1 with the week (Monday through Sunday) that contains the year's first Thursday, MongoDB 3.4 and newer provided the $isoweek operator for use.

Upvotes: 3

Vaibhav Patil
Vaibhav Patil

Reputation: 3013

try this:

db.collection.find(
{$or:[{"key1":{$exists:true}},{"key2":{$exists:true}}],
timeStamp: {$gte: Date(2016, 08, 01), $lte:Date(2016, 08, 31)}
})

above query fulfilled your 1 and 2 condition.What is your 3rd condition I do not understand fully, please elaborate.

Upvotes: 1

Related Questions