How can I extract whole documents based on how they compare with their whole collection?

Question

I'm trying to extract the latest available daily measurements from a "sparse" collection that might not have a measurement for every day. I'm interested in getting the whole original document as output. The collection contains several series of measurements identified by a unique id.

For example, given the following collection:

{ "date" : "2019-04-10", "id" : 1, "measurement" : 50 }
{ "date" : "2019-04-10", "id" : 2, "measurement" : 1 }
{ "date" : "2019-04-10", "id" : 3, "measurement" : 33 }
{ "date" : "2019-04-11", "id" : 1, "measurement" : 52 }
{ "date" : "2019-04-11", "id" : 3, "measurement" : 3 }
{ "date" : "2019-04-12", "id" : 1, "measurement" : 55 }
{ "date" : "2019-04-12", "id" : 2, "measurement" : 12 }

The above collection contains measurements for 3 ids. I'd like to retrieve the latest measurements for each id.

For example, the above collection should yield the following result:

{ "date" : "2019-04-12", "id" : 1, "measurement" : 55 }
{ "date" : "2019-04-12", "id" : 2, "measurement" : 12 }
{ "date" : "2019-04-11", "id" : 3, "measurement" : 3 }

So far, I'm able to extract the latest date for every ids with this:

db.control_subs.aggregate([ { $group : { _id : "$id", "last_date" : { $max : "$date" } } }, { $sort:{ "_id": 1 }} ])

But this, unfortunately, strips the actual measurement field from the output.

How could I obtain the desired output with a single MongoDB query?

possible duplicate of https://stackoverflow.com/questions/33361697/get-all-the-documents-having-max-value-using-aggregation-in-mongodb?rq=1 — krishna Prasad, Apr 22 '19 at 18:04

score 0 · Accepted Answer · answered Apr 22 '19 at 18:03

You can try below aggregation query with $$ROOT operator:

db.control_subs.aggregate([ 
    {
        "$project": 
            { 
                "id": "$id",
                "date": "$date", 
                "document": "$$ROOT" // save all fields for future usage
        }
    }, 
    { 
        "$sort": 
            { "date": -1 
        }
    }, 
    {
        "$group":
            {
                "_id":{"id":"$id"},
                "original_doc":{"$first":"$document"} 
        }
    }, 
    {
        $project: 
            {
                "original_doc.date":1, "original_doc.id":1, "original_doc.measurement":1, _id:0}
        } 
])

Output of above aggregation is

{ "original_doc" : { "date" : "2019-04-11", "id" : 3, "measurement" : 3 } }
{ "original_doc" : { "date" : "2019-04-12", "id" : 2, "measurement" : 12 } }
{ "original_doc" : { "date" : "2019-04-12", "id" : 1, "measurement" : 55 } }

Even you can also replace the original_doc with the help of $replaceRoot

After getting above output you can again sort on the basis of `id` and replace the `original_doc` as suggested. — krishna Prasad, Apr 22 '19 at 18:06

How can I extract whole documents based on how they compare with their whole collection?

1 Answers1