1

I have a collection like this:

{'speed':45, 'time':1446271000},   
{'speed':45, 'time':1446271001},   
{'speed':63, 'time':1446271002},    
{'speed':68, 'time':1446271003},  
{'speed':70, 'time':1446271004},    
{'speed':59, 'time':1446271005},   
{'speed':55, 'time':1446271006},    
{'speed':61, 'time':1446271007},    
{'speed':62, 'time':1446271008},   
{'speed':63, 'time':1446271009},    
{'speed':67, 'time':1446271010}   

I want to summarise the high speed (speed >=60) records, so the result should look like:

{'speed':63,'duration':2,'start': 1446271002,'end': 1446271004}  
{'speed':61,'duration':3,'start': 1446271007,'end': 1446271010}

How should I achieve this?

1
  • 1
    do you want the results to be sorted by start time? Commented Oct 31, 2015 at 12:08

2 Answers 2

2

Use the following aggregation pipeline which does an initial $match to filter out those documents that are less than 60.

The next pipeline step uses the $sort operator to re-order the documents by the time field which is necessary for the next step, i.e. the $group pipeline. In here that's where you derive the start and end fields through the use of the $first and $last accumulator operators that extract the first and last times when you group the documents by the speed field as the key.

The last pipeline step $project creates the additional field, duration using the $subtract arithmetic operator which, as the name implies, subtracts the start from the end times. The final pipeline would look like this:

db.test.aggregate([
    { "$match": { "speed": { "$gte": 60 } } },
    { "$sort": { "time": 1 }  },
    {
        "$group": {
            "_id": "$speed",
            "start": { "$first": "$time" },
            "end": { "$last": "$time" }
        }
    },
    {
        "$project": {
            "_id": 0,
            "speed": "$_id",
            "duration": { "$subtract": [ "$end", "$start" ] },
            "start": 1,
            "end": 1
        }
    }
])

Sample Output:

/* 0 */
{
    "result" : [ 
        {
            "start" : 1446271010,
            "end" : 1446271010,
            "speed" : 67,
            "duration" : 0
        }, 
        {
            "start" : 1446271007,
            "end" : 1446271007,
            "speed" : 61,
            "duration" : 0
        }, 
        {
            "start" : 1446271008,
            "end" : 1446271008,
            "speed" : 62,
            "duration" : 0
        }, 
        {
            "start" : 1446271004,
            "end" : 1446271004,
            "speed" : 70,
            "duration" : 0
        }, 
        {
            "start" : 1446271003,
            "end" : 1446271003,
            "speed" : 68,
            "duration" : 0
        }, 
        {
            "start" : 1446271002,
            "end" : 1446271009,
            "speed" : 63,
            "duration" : 7
        }
    ],
    "ok" : 1
}
Sign up to request clarification or add additional context in comments.

1 Comment

After you add $sort stage, it will be perfect.
0

I am posting this because the existing answer add unnecessary $sort stage to the pipeline which will result in the drop of performance.

You need to filter out all document in your collection where speed is $gte 60 using the $match operator then $group your document by "speed" and use the $min and $max accumulator operators which respectively return the mimimum and maximum value for "time" for distinct group. From there you need to $projection stage in other to calculate to "duration" using the $subtract operator. Of course the .aggregate() method provides access to the aggregation pipeline.

db.collection.aggregate([ 
    { "$match": { 
        "speed": { "$gte": 60 } 
    }}, 
    { "$group": { 
        "_id": "$speed", 
        "duration": { "$sum": 1 }, 
        "start": { "$min": "$time" }, 
        "end": { "$max": "$time" }
    }},
    { "$project": { 
        "speed": "$_id", 
        "_id": 0, 
        "duration": { "$subtract": [ "$end", "$start" ] }, 
        "start": 1, 
        "end": 1 
    }} 
])

Which yield:

{ "start" : 1446271010, "end" : 1446271010, "speed" : 67, "duration" : 0 }
{ "start" : 1446271008, "end" : 1446271008, "speed" : 62, "duration" : 0 }
{ "start" : 1446271007, "end" : 1446271007, "speed" : 61, "duration" : 0 }
{ "start" : 1446271004, "end" : 1446271004, "speed" : 70, "duration" : 0 }
{ "start" : 1446271003, "end" : 1446271003, "speed" : 68, "duration" : 0 }
{ "start" : 1446271002, "end" : 1446271009, "speed" : 63, "duration" : 7 }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.