MongoDB get first and last document in aggregate query

Question

How can I get first and last document based on time field. I can use $group and get $first and $last document, but I don't need grouping here, just get first and last full document. Maybe I could use slice? This query doesn't work:

{
  "aggregate": "353469045637980_data",
  "pipeline": [
    {
      "$match": {
        "$and": [
          {
            "time": {
              "$gte": 1461369600
            }
          },
          {
            "time": {
              "$lt": 1461456000
            }
          }
        ]
      }
    },
    {
      "$project": {
        "first": {
          "$slice": 1
        },
        "last": {
          "$slice": -1
        }
      }
    }
  ]
}

Answer 1

Well you need $group but you can simply use a constant (e.g. null, see the docs) for its id so that it results in a single group. $$ROOT then refers to the document itself which you can use with $first and $last like so

$group: {
  _id: null,
  first: { $first: "$$ROOT" },
  last: { $last: "$$ROOT" }
}

Of course you can introduce further $project stages to shape that data into an array (as you mentioned you want a list) etc.

As a side note you may want to introduce a $sort stage to make sure $first and $last have a proper meaning.

Answer 2

You can make use of a $facet stage, combined with $sort/$limit stages:

// { time: ISODate("2021-12-04"), b: "hello" }
// { time: ISODate("2021-12-07"), b: "world" }
// { time: ISODate("2021-12-05"), b: "oups"  }
db.collection.aggregate([
  { $facet: {
    first: [{ $sort: { time: 1 } }, { $limit: 1 }],
    last:  [{ $sort: { time: -1 } }, { $limit: 1 }]
  }},
  { $set: { first: { $first: "$first" }, last: { $last: "$last" } } }
])
// { first: { time: ISODate("2021-12-04"), b: "hello" }, last: { time: ISODate("2021-12-07"), b: "world" } }

The $facet stage allows us to run multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its result is stored as an array of documents.

Each field is thus produced by its own aggregation pipeline whose first stage $sorts documents by the time field (in opposite orders), followed by a $limit stage that'll only keep the first item (the smallest as defined by the chosen ordering).

The second part of the pipeline (the $set stage) is just there to clean-up the $facet output format.

---

Note that a $sort followed by a $limit stage is optimised by Mongo (see here). This allows the sort operation to only maintain the top n results (in our case only 1 element) as it progresses.

Also note that our $sort stages will benefit from having an index on time.