Query a collection for the latest unique object

Question

Question: Lets say I have the following objects in a collection:

How would I return one record per "product_id" and only the one with the highest "version" number? And is this possible to do within mongoose?

{
    "_id" : ObjectId("54f765564b10883c1800002a"),
    "total_invoice_fob_case" : 86.70999999999999,
    "status" : "Draft",
    "discount" : "3.40",
    "effective_date" : ISODate("2013-08-01T06:00:00.000Z"),
    "version" : 2,
    "controlstate" : "AB",
    "controlstate_id" : ObjectId("54d510e9e3d793f581b6bb27"),
    "product" : "Product A",
    "product_id" : ObjectId("54f75b5e4b1088801a000627"),
    "size" : "1.75LTR",
    "size_id" : ObjectId("5418a3dd750b4294c2cb3a47"),
    "vendor" : "BEAM SUNTORY",
    "vendor_id" : ObjectId("54ef5aa74b1088781b000169"),
    "product_state_code" : "123",
    "net_fob_cost" : 86.70999999999999,
    "change_reason" : [ 
        "Other"
    ],
    "submitted" : {
        "submitted_date" : ISODate("2014-05-16T06:00:00.000Z")
    }
},
{
    "_id" : ObjectId("54f765564b10883c1800002b"),
    "total_invoice_fob_case" : 86.70999999999999,
    "status" : "Draft",
    "discount" : "4.40",
    "effective_date" : ISODate("2013-08-01T06:00:00.000Z"),
    "version" : 3,
    "controlstate" : "AB",
    "controlstate_id" : ObjectId("54d510e9e3d793f581b6bb27"),
    "product" : "Product A",
    "product_id" : ObjectId("54f75b5e4b1088801a000627"),
    "size" : "1.75LTR",
    "size_id" : ObjectId("5418a3dd750b4294c2cb3a47"),
    "vendor" : "BEAM SUNTORY",
    "vendor_id" : ObjectId("54ef5aa74b1088781b000169"),
    "product_state_code" : "123",
    "net_fob_cost" : 86.70999999999999,
    "change_reason" : [ 
        "Other"
    ],
    "submitted" : {
        "submitted_date" : ISODate("2014-05-16T06:00:00.000Z")
    }
},
{
    "_id" : ObjectId("54f765564b10883c1800002c"),
    "total_invoice_fob_case" : 86.70999999999999,
    "status" : "Draft",
    "discount" : "3.40",
    "effective_date" : ISODate("2013-08-01T06:00:00.000Z"),
    "version" : 2,
    "controlstate" : "AB",
    "controlstate_id" : ObjectId("54d510e9e3d793f581b6bb27"),
    "product" : "Product B",
    "product_id" : ObjectId("54f75b5e4b1088801a000628"),
    "size" : "1.75LTR",
    "size_id" : ObjectId("5418a3dd750b4294c2cb3a47"),
    "vendor" : "BEAM SUNTORY",
    "vendor_id" : ObjectId("54ef5aa74b1088781b000169"),
    "product_state_code" : "123",
    "net_fob_cost" : 86.70999999999999,
    "change_reason" : [ 
        "Other"
    ],
    "submitted" : {
        "submitted_date" : ISODate("2014-05-16T06:00:00.000Z")
    }
}

Answer 1

Sounds like a job for Mongo's aggregation framework. You can extrapolate from this example how to approach the problem.

Update: To retrieve one per product_id with the highest version you would need to also use $first:

db.products.aggregate([
  {$sort: {product_id: 1, version: -1}}, // sort first so that $first pulls the correct record
  {$group: {
    _id: {product_id: '$product_id'}, // group by the product_id
    product: {$first: $$ROOT} // only return the first document per group
  }}
]);

Answer 2

You need an aggregation pipeline that first sorts the documents in the collection by version number descending using a $sort pipeline stage, then groups the ordered documents by product_id using the $group operator. Within the grouping use the $first operator on $$ROOT to return the first document in the sorted group:

var pipeline = [
    {
        "$sort": { "version": -1 }
    },
    {
        "$group": {
            "_id": "$product_id",
            "value": {
                "$first": "$$ROOT"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "product_id": "$_id",
            "status": "$value.status",
            "version": "$value.version",
            "product" : "$value.product"
        }    
    }
 ];

// Mongoose aggregation
Model.aggregate(pipeline, function (err, res) {
  if (err) return handleError(err);
  console.log(res); // 
});

Console Output:

[ 
    {
        "product_id" : ObjectId("54f75b5e4b1088801a000628"),
        "status" : "Draft",
        "version" : 2,
        "product" : "Product B"
    }, 
    {
        "product_id" : ObjectId("54f75b5e4b1088801a000627"),
        "status" : "Draft",
        "version" : 3,
        "product" : "Product A"
    }
]

-- UPDATE --

To project the full document, replace the $project pipeline with the following:

{
    "$project": {
        "_id": 0,
        "product": "$value"
    }
}

Output:

/* 1 */
{
    "result" : [ 
        {
            "product" : {
                "_id" : ObjectId("54f765564b10883c1800002c"),
                "total_invoice_fob_case" : 86.7099999999999940,
                "status" : "Draft",
                "discount" : "3.40",
                "effective_date" : ISODate("2013-08-01T06:00:00.000Z"),
                "version" : 2,
                "controlstate" : "AB",
                "controlstate_id" : ObjectId("54d510e9e3d793f581b6bb27"),
                "product" : "Product B",
                "product_id" : ObjectId("54f75b5e4b1088801a000628"),
                "size" : "1.75LTR",
                "size_id" : ObjectId("5418a3dd750b4294c2cb3a47"),
                "vendor" : "BEAM SUNTORY",
                "vendor_id" : ObjectId("54ef5aa74b1088781b000169"),
                "product_state_code" : "123",
                "net_fob_cost" : 86.7099999999999940,
                "change_reason" : [ 
                    "Other"
                ],
                "submitted" : {
                    "submitted_date" : ISODate("2014-05-16T06:00:00.000Z")
                }
            }
        }, 
        {
            "product" : {
                "_id" : ObjectId("54f765564b10883c1800002b"),
                "total_invoice_fob_case" : 86.7099999999999940,
                "status" : "Draft",
                "discount" : "4.40",
                "effective_date" : ISODate("2013-08-01T06:00:00.000Z"),
                "version" : 3,
                "controlstate" : "AB",
                "controlstate_id" : ObjectId("54d510e9e3d793f581b6bb27"),
                "product" : "Product A",
                "product_id" : ObjectId("54f75b5e4b1088801a000627"),
                "size" : "1.75LTR",
                "size_id" : ObjectId("5418a3dd750b4294c2cb3a47"),
                "vendor" : "BEAM SUNTORY",
                "vendor_id" : ObjectId("54ef5aa74b1088781b000169"),
                "product_state_code" : "123",
                "net_fob_cost" : 86.7099999999999940,
                "change_reason" : [ 
                    "Other"
                ],
                "submitted" : {
                    "submitted_date" : ISODate("2014-05-16T06:00:00.000Z")
                }
            }
        }
    ],
    "ok" : 1
}