How to query only documents with the latest timest

问题:

In the MongoDB collection I'm querying, each document represents an item at a specific time. When updating a document, a new document is created with the same item id and a new timestamp. All items have unique item ids.

To illustrate, consider this example. We start with one revision of an item:

{
    _id: x,
    itemId: 123,
    createdOn: ISODate("2013-01-30T11:16:20.102Z"),
    field1: "foo",
    field2: "bar
}

After an update, we have two revisions of the item, with the same itemId and different timestamps.

[{
  _id: x,
  itemId: 123,
  createdOn: ISODate("2013-01-30T11:16:20.102Z"),
  field1: "foo",
  field2: "bar"
},
{
  _id: y,
  itemId: 123,
  createdOn: ISODate("2014-02-09T14:26:20.102Z"),
  field1: "baz",
  field2: "fiz"
}]

How can I find all items that in their most recent revision satisfy a certain query?

My current (wrong) approach is to first find the matching documents, then sort by timestamp, group them by itemId, and return the values from the first document in the group:

ItemModel.aggregate({ $match: { field1: "foo"} }).sort({createdOn: -1}).group(
    {
        _id: '$itemId', // grouping key
        createdOn: {$first: '$createdOn'},
        field1: {$first: '$field1'},
        field2: {$first: '$field2'}
    }).exec(...);

This is wrong because it matches old revisions of items. Only the latest revisions of items should match. In the example above, this approach returns item "123", while the correct result is an empty result set.

回答1:

You are mixing a few methods here when you can be doing everything in the aggregation pipeline. Otherwise it's just a matter of getting your steps in the right order:

db.collection.aggregate([
    {$sort: { createdOn: -1 }},
    {$group: { _id: "$itemId", 
        createdOn: {$first: "$createdOn"},
        field1: {$first: "$field1" },
        field2: {$first: "$field2" }
    }},
    {$match: { field1: "foo" }}
])

So sort first for newest documents. Group on the itemId ( order will be maintained for $first ), and then filter with $match if you must. But your grouped documents will be latest ones.

回答2:

One can consider changing the document's schema to better suit your queries, and reduce the overhead of aggregation. Instead of creating a new document for each revision, you could push revision sub-documents onto an array and maintain the latest revision at the parent document; for example:

{
    _id: x,
    itemId: 123,
    createdOn: ISODate("2014-02-09T14:26:20.102Z"),
    field1: "baz",
    field2: "fiz,
    revisions: [
        {createdOn: ISODate("2013-01-30T11:16:20.102Z"), field1: "foo", field2: "bar"},
        {createdOn: ISODate("2014-02-09T14:26:20.102Z"), field1: "baz", field2: "fiz"}
    ]
}

Keep in mind that MongoDB enforces a document-size limit of 16MB; this should suffice for most use cases. This would make your queries very simple: db.collection.find({field1: "foo"})

Just another approach...