Mongodb group documents and limit each group

Question

Considering those documents:

{
    "Field1":"Test_1",
    "Speaker":1,
    "Listener":2,
    "ListenTime": ISODate("2016-10-15T14:17:49.336Z")
},
{
    "Field1":"Test_2",
    "Speaker":1,
    "Listener":2,
    "ListenTime": ISODate("2016-10-13T14:17:49.336Z")
},
{
    "Field1":"Test_3",
    "Speaker":1,
    "Listener":3,
    "ListenTime": ISODate("2016-10-10T14:10:49.336Z")
}

What i what to do is to extract with a single query in MongoDB (3.2) only those documents grouping them by Speaker and Listener, taking only the document with the oldest ListenTime.
So, in this case, the result will be:

{
    "Field1":"Test_1",
    "Speaker":1,
    "Listener":2,
    "ListenTime": ISODate("2016-10-15T14:17:49.336Z")
},
{
    "Field1":"Test_3",
    "Speaker":1,
    "Listener":3,
    "ListenTime": ISODate("2016-10-10T14:10:49.336Z")
}

Is it possible do that with a single query?

Answer 1

Run the following aggregation pipeline to get the desired result:

db.collection.aggregate([
    { "$sort": { "ListenTime": -1 } },
    {
        "$group": {
            "_id": {
                "Speaker": "$Speaker",
                "Listener": "$Listener"
            },
            "Field1" : { "$first": "$Field1" },
            "ListenTime" : { "$first": "$ListenTime" }
        }
    },
    {
        "$project": {
            "Field1": 1,
            "Speaker": "$_id.Speaker",
            "Listener": "$_id.Listener",
            "ListenTime": 1,
            "_id": 0
        }
    }
])

Sample Output

/* 1 */
{
    "Field1" : "Test_3",
    "ListenTime" : ISODate("2016-10-10T14:10:49.336Z"),
    "Speaker" : 1,
    "Listener" : 3
}

/* 2 */
{
    "Field1" : "Test_1",
    "ListenTime" : ISODate("2016-10-15T14:17:49.336Z"),
    "Speaker" : 1,
    "Listener" : 2
}

Answer 2

The aggregation framework can be used to achieve this. In the first stage, use $group to group the documents by Speaker and Listener. In this stage, add all the documents in the group to a list using the $push operator and using the $max operator calculate the recent ListenTime. Follow this, by a $redact stage to retain the document with the recent ListenTime in the list. Then, use an $unwind stage to flatten the list into documents. Then, use a final $project stage, to get the desired fields.

The aggregation query will look like this.

db.sampleCollection.aggregate([
    {
        "$group":{
            "_id":{"Speaker":"$Speaker", "Listener":"$Listener"}, 
            ListenTime : {"$max":"$ListenTime"}, 
            "docs":{"$push":"$$ROOT"}
        }
    },
    {
        $redact:{
            $cond:[{$eq:["$ListenTime","$$ROOT.ListenTime"]},"$$DESCEND","$$PRUNE"]
        }
    },

    {
        "$project":{
            "ListenTime":1, 
            "Field1":"$docs.0.Field1", 
            "Speaker":"$docs.0.Speaker", 
            "Listener":"$docs.0.Listener"
        }
    }
])

Sample output:

{
        "ListenTime" : ISODate("2016-10-10T14:10:49.336Z"),
        "Field1" : "Test_3",
        "Speaker" : 1,
        "Listener" : 3
}
{
        "ListenTime" : ISODate("2016-10-15T14:17:49.336Z"),
        "Field1" : "Test_1",
        "Speaker" : 1,
        "Listener" : 2
}