Fetch first N and last N records from MongoDB collection

Question

I have a use case where I need to show top 10 and last 10 results from a group and sorted aggregation. I tried to use $limit but that will not let the next aggregators to work on the complete data.

db.collection.aggregate([groupAggregator, sortAggregator, { $limit: 10 }, /*only 10 records available*/]

How to perform aggregations on the whole collection in the middle of the pipeline? I am using MongoDB 3.2.9. If that's impossible is there a way to UNION two aggregations the first one being the top 10 (ASC SORTED) and the second one last 10 (DESC SORTED),

Had it not been for the group aggregation I would have used the db.collection.find({}).sort().filter() strategy but the group needs to be done.

Data obtained from group aggregation

{_id: "", ..., avg_count: 10}
{_id: "", ..., avg_count: 1}
{_id: "", ..., avg_count: 2}
{_id: "", ..., avg_count: 5}
{_id: "", ..., avg_count: 8}
{_id: "", ..., avg_count: 3}
{_id: "", ..., avg_count: 4}
{_id: "", ..., avg_count: 6}
{_id: "", ..., avg_count: 7}
{_id: "", ..., avg_count: 9}

Data obtained from Sort aggregation

{_id: "", ..., avg_count: 1}
{_id: "", ..., avg_count: 2}
{_id: "", ..., avg_count: 3}
{_id: "", ..., avg_count: 4}
{_id: "", ..., avg_count: 5}
{_id: "", ..., avg_count: 6}
{_id: "", ..., avg_count: 7}
{_id: "", ..., avg_count: 8}
{_id: "", ..., avg_count: 9}
{_id: "", ..., avg_count: 10}

DESIRED OUTPUT:

FETCH FIRST 2 AND LAST 2 documents

{_id: "", ..., avg_count: 1}
{_id: "", ..., avg_count: 2}
{_id: "", ..., avg_count: 9}
{_id: "", ..., avg_count: 10}

NOTE: The above is just a sample data, actual data does not have exact serial numbers.

Can you perhaps add some sample data and the desired output? Based on your description I'm having a hard time figuring out what exactly it is you're after. — dnickless
– dnickless, Commented Dec 14, 2017 at 21:00

dnickless · Accepted Answer · 2017-12-14 21:36:35Z

5

If understand you correctly, here's one way of getting that behaviour:

db.collection.aggregate([{
    $sort: { "your_sort_field": 1 } // sort the data
}, {
    $group: {
        _id: null, // group everything into one single bucket
        docs: { $push: "$$ROOT" } // push all documents into an array (this will be massive for huge collections...)
    }
}, {
    $project: {
        "docsTop10": { $slice: [ "$docs", 10 ] }, // take the first 10 elements from the ASC sorted array
        "docsBottom10": { $reverseArray: { $slice: [ "$docs", -10 ] } } // take the last 10 elements from the array but reverse their order
    }
}])

If you want to have everything in one single property you can simply use $concatArrays in the final stage:

$project: {
    "result": { $concatArrays: [ { $slice: [ "$docs", 10 ] }, { $reverseArray: { $slice: [ "$docs", -10 ] } } ] }
}

Unfortunately, there's no $replaceRoot yet in your version of MongoDB, otherwise you could flatten the results more nicely.

Also, since $reverseArray does not seem to be available in v3.2, either, you can simply drop that operator and $unwind and $sort one more time after the $project stage:

{
    $project: {
        _id: 0,
        "result": { $concatArrays: [ { $slice: [ "$docs", 10 ] }, { $slice: [ "$docs", -10 ] } ] }
    }
}, {
    $unwind: "$result"
}, {
    $sort: { "result.your_sort_field": 1 } // sort the data
}

Another option would be to use $facet (only from v3.4 onwards) which would certainly be way faster as MongoDB would be able to nicely optimize the sort/limit combination:

db.collection.aggregate([{
    $facet: { // start two separate pipeline
        "docsTop10": [
            { $sort: { "your_sort_field": 1 } }, // sort ASC
            { $limit: 10 } // take top 10
        ],
        "docsBottom10": [
            { $sort: { "your_sort_field": -1 } }, // sort DESC
            { $limit: 10 } // take top 10
        ]
    }
}])

edited Dec 14, 2017 at 21:36

answered Dec 14, 2017 at 21:07

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

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

2 Comments

Nandu Kalidindi Over a year ago

I am dealing with rows of order 100K but the first could be of little help for now. Sadly reverseArray is not compatible with Mongo 3.2.9.

Nandu Kalidindi Over a year ago

Great answer! Thank you for both workarounds, I upgraded to Mongo 3.6 to leverage $facet aggregation.

Collectives™ on Stack Overflow

Fetch first N and last N records from MongoDB collection

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related