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
        }
      }
    }
  ]
}

First and last value of what? Can you be more specific, perhaps include a bunch of test documents and your expected output? — chridam
– chridam, Commented Aug 16, 2016 at 12:59
I'm expecting list with two entries [first, last]. Where first and last are full documents from collection — Michał Jurczuk
– Michał Jurczuk, Commented Aug 16, 2016 at 13:02

DAXaholic · Accepted Answer · 2016-08-16 13:21:11Z

26

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.

answered Aug 16, 2016 at 13:21

DAXaholic

35.9k6 gold badges84 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Uzumaki Naruto Over a year ago

Did you write the query ? I still don't understand. Can you please share the final query to get the first and last document based on creation date

Nandy Over a year ago

Is there any way to get last two documents on the group by stage?

Xavier Guihot · Accepted Answer · 2021-12-04 21:46:08Z

1

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.

answered Dec 4, 2021 at 21:46

Xavier Guihot

62.8k26 gold badges320 silver badges202 bronze badges

3 Comments

newman Over a year ago

Does $sort here really use index? I have a collection with 11 million doc, time is indexed. It takes 0.001 sec if I do db.collection.find({}).sort({time: 1}).limit(1), but it takes >73 seconds if I do aggregate as you suggested. Any explanation?

Xavier Guihot Over a year ago

@newman Damn, you're right: docs.mongodb.com/manual/reference/operator/aggregation/facet/… . That's quite sad, there's no real reason not to.

GMaiolo Over a year ago

Thanks for this answer, $facet really makes sense for this, and it's very semantic at the same time.

Mathias · Accepted Answer · 2021-06-22 11:18:44Z

I know this does not answer the question directly but would have helped me a lot when I came across this question.

You could (like DAXaholic explained) use the following query to get the corresponding documents:

[
  $sort: {...},
  $group: {
    _id: null,
    first: { $first: "$$ROOT" },
    last: { $last: "$$ROOT" }
  }
]

The problem I faced was that I got the following error: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.

To work around this issue (without using disk for sorting) you could use:

[
  $group: {
    _id: null,
    first: { $min: "$time" },
    last: { $max: "$time" }
  }
]

The advantage is that you don't need to sort and therefore might not run in the 100 MB memory limit. The downside is that it does not return the entire document (where $min / $max matches) but only the value of the property (in this example time).

Collectives™ on Stack Overflow

MongoDB get first and last document in aggregate query

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related