[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:
Related
related to SERVER-7528 Order of responses to a MongoDB $in q... Open
Operating System: ALL
Steps To Reproduce:

1. Get some test docs with array field, filled with ObjectId elements.
2. Run this query.

db.getCollection('trades').aggregate([
{  '$match': {
        _id: ObjectId('5a546428f228c3156579fe00')
    } 
}, 
{ 
    '$lookup': { 
        from: 'images',
        localField: 'images',
        foreignField: '_id',
        as: 'images'
    }
}
]);

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:

{
"_id" : ObjectId("5a0bf58c9616e5016426d5ef"),
"categoryId" : 101,
"name" : "Some name of something",
"price" : 50000,
"images" : [ 
    ObjectId("5a0bf5349616e5016426d5e9"),
    ObjectId("5a0bf6ac9616e5016426d5f7")
],
}

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.

db.getCollection('trades').aggregate([
{  '$match': {
        _id: ObjectId('5a546428f228c3156579fe00')
    } 
}, 
{ 
    '$lookup': { 
        from: 'images',
        localField: 'images',
        foreignField: '_id',
        as: 'images'
    }
}
]);



 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,
Mark

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