[SERVER-32947] Does mongodb $lookup operator ruins default array order? Created: 29/Jan/18 Updated: 23/Mar/21 Resolved: 30/Jan/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 3.4.10 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Pavel | Assignee: | Mark Agarunov |
| Resolution: | Done | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||
| Operating System: | ALL | ||||||||||||||
| Steps To Reproduce: | 1. Get some test docs with array field, filled with ObjectId elements.
3. Default order is ruined. |
||||||||||||||
| Participants: | |||||||||||||||
| Description |
|
I faced with some strange things when I was working with mongoose. I got mongodb collection with some docs:
When I join images in "images" field by ObjectId, $lookup operator jumble up default order of array. Why is this happening? When I use third-party ODM (Mongoose) to join images default order of images array is preserved.
|
| Comments |
| Comment by Craig Marvelley [ 23/Mar/21 ] |
|
Asya Kamsky – thanks for that info, that's good to know and is a better solution than what I ended up doing. It'd be really helpful if we didn't have to do anything extra at all though |
| Comment by Asya Kamsky [ 23/Mar/21 ] |
|
The SO answer you link is wrong - it's incorrectly describing what happens with $lookup and array and the work-around it gives is incorrect and unnecessarily involves $expr and $unwind. Simple workaround is to preserve original array and use $indexOf (similar to the way described here: http://www.kamsky.org/stupid-tricks-with-mongodb/using-34-aggregation-to-return-documents-in-same-order-as-in-expression) |
| Comment by Craig Marvelley [ 22/Mar/21 ] |
|
I'd like to also voice my frustration with this – the only way I can see to restore the original order is to do something like this, but that won't work in all use cases. Using $match/$expr is often a no-go for performance reasons, and $unwinding is problematic when the original documents are large and complex with dynamic properties, because putting them back together again afterward is tricky. In my opinion the sensible thing to do would be to maintain the order from the original array that was provided to the $lookup stage. I understand it may not be that simple but it would result in far less work and confusion for developers using this feature. |
| Comment by Mika Fischer [ 13/Feb/20 ] |
|
But what if the desired order is the order of the ids in the array? That seems to me to be a reasonable use-case and I see no easy way to do that. Am I missing something? |
| Comment by Mark Agarunov [ 30/Jan/18 ] |
|
Hello penpaul, Thank you for the report. When using $lookup, the order of the documents returned is not guaranteed. The documents are returned in "natural order" - as they are encountered in the database. The only way to get a guaranteed consistent order is to add a $sort stage to the query. If the documents are sorted in the ODM you are using, it's likely inserting a $sort or sorting the objects in the ODM itself. Thanks, |