[SERVER-32151] Would like the ability to use the index for sorting an array of sub-documents (aggregation pipeline) Created: 01/Dec/17  Updated: 27/Oct/23  Resolved: 04/Dec/17

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Emil Arsa Assignee: Mark Agarunov
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

Correct me if I'm wrong, I believe that it is currently impossible to sort an array of sub-documents while using its index.

Take for example this collection of exactly 1 document (and imagine it has 10,000 sub-documents in the `readers` array.) The collection has an index on `readers.name`.

{_id: 1, book: "The Great Book", readers: [
{_id: 1, name: "Joe", sex: "male"},{_id: 2, name: "Jane", sex: "female"}]}

As soon as `$unwind` is used in an aggregation pipeline, the aggregation pipeline stops using all indexes.

  • My first step in the aggregation pipeline would be to use `$match` to match ` {book: "The Great Book}

    `.

  • My second step in the aggregation pipeline would be to `$unwind` the `readers`. ====> (So, if I understand correctly, I won't be able to make use of indexes after this step!?)
  • My third step in the aggregation pipeline would be to sort by `"readers.name"`. (If I understand correctly, this third step cannot make use of an index?!)</p>

Is there a different way (that I cannot see) of doing this sort without clogging the memory (by not using the index)?



 Comments   
Comment by Mark Agarunov [ 04/Dec/17 ]

Hello emilio911

Thank you for the report. MongoDB indexes only support sorting documents, not array elements, and that’s why the index cannot be used after the $unwind stage to sort the elements of the array. This is because after the $unwind stage, the documents returned no longer match the layout of the documents stored in the collection and index.

Depending on your needs, some options would be to insert these elements at the top level in the collection, a document for each reader, which would allow sorting with the index, and would give the same output as unwinding the array. You could then use $group and $project to adjust the results to your desired schema. Alternatively, you could sort the documents on insertion instead of with the aggregation by using the $push, $each, and $sort stages.

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group.

Thanks,
Mark

Generated at Thu Feb 08 04:29:20 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.