[SERVER-30385] Lookup an array ob ObjectId doesn't guaranty the order Created: 28/Jul/17  Updated: 27/Oct/23  Resolved: 07/Aug/17

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

Type: Bug Priority: Major - P3
Reporter: CAO Quang Binh Assignee: Mark Agarunov
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

I have 2 collections:

  • Foo (_id, bars[])
  • Bar (_id)

`bars` is the array of `Bar`'s ObjectId.

I would like to use `lookup` to get the array of `Bar` in EXACTLY order that the Id stored in `bars`.

{
	$lookup: {
		from: 'bars',
		localField: 'bars,
		foreignField: '_id',
		as: 'bars'
	}
}

Everything are ok, UNTIL I $push another Bar's ObjectId to BEGIN of the `bars` array. The $lookup will returns the array which the new one is in the end of array, NOT begin of array as my expectation.

For example:

{
	_id: ...,
	bars: [A_id, B_id]
}

=> A, B
But, after $push C_id to BEGIN of array:

{
	_id: ...,
	bars: [C_id, A_id, B_id]
}

=> A, B, C
And so on for D_id => A, B, C, D

My expectation is: A, B, C, D.



 Comments   
Comment by Asya Kamsky [ 29/Jul/19 ]

The workaround can be similar to the one described here which is to use original array and $map to get the order matching original array. This avoids $unwind and $group which is desirable as $group is a blocking stage and will slow things down most likely.

Comment by CAO Quang Binh [ 07/Aug/17 ]

Hi @Charlie Swanson,

Thank you for your reply.

Your solution is the same as the one I describe above.

So, I'll consider this is the "official solution" for my purpose.

Comment by Charlie Swanson [ 01/Aug/17 ]

Hi binh.cao,

Note that we do not guarantee anything about the order of results coming back from the $lookup. If you require them to be in a particular order, you must sort them yourself. This lookup is the equivalent of doing a $in query with each of the values in 'data.users', which would not provide any ordering either.

One way you could do this is to do the following:

> db.foo.find()
{ "_id" : 1, "bars" : [ "A", "C", "B" ] }
> db.bar.find()
{ "_id" : "B" }
{ "_id" : "C" }
{ "_id" : "A" }
> db.foo.aggregate([
   {$unwind: {path: "$bars", includeArrayIndex: "arrIndex"}},
   {$lookup: {from: "bar", localField: "bars", foreignField: "_id", as: "bars"}},
   {$unwind: "$bars"},
   {$sort: {arrIndex: 1}},
   {$group: {_id: "$_id", bars: {$push: "$bars"}}}
 ])
{ "_id" : 1, "bars" : [ { "_id" : "A" }, { "_id" : "C" }, { "_id" : "B" } ] }

Comment by CAO Quang Binh [ 01/Aug/17 ]

Hi @Mark Agarunov,
Thank you for your information.
This is my $push update:

this.update({
	$push: {
		'data.users': {
			$each: [userId],
			$position: 0
		}
	}
}, callback)

And the $lookup:

{
	$lookup: {
		from: 'users',
		localField: 'data.users',
		foreignField: '_id',
		as: 'data.users'
	}
}

If you need any infomation, just tell me, I'll give it to you.

Comment by Mark Agarunov [ 31/Jul/17 ]

Hello binh.cao,

Thank you for the information. If possible please provide the exact queries you are using, including the $push query being used so that we can reproduce this exactly the same way.

Thank you,
Mark

Comment by CAO Quang Binh [ 29/Jul/17 ]

Hi @Mark Agarunov,

I did use the $position modifier.

As I described, I $push the C_id to the BEGIN of bars field. In the db, I got this order: C_id, A_id, B_id.

However, after $lookup, I got: A, B, C.

C is still in the END of line, although in db, it's in the BEGIN of line.

p/s: my current solution is:
1. $unwind the bars field
2. $lookup for the Bar object
3. $group the result based on the Foo_id
4. other fields in FOO, I use $first modifier to pick them, such as:

$group: {
  _id: ...,
 lorem: {$first: '$lorem'} 
}

However, I still need the official solution: the order after the lookup is exactly the same as the order in db.

Comment by Mark Agarunov [ 28/Jul/17 ]

Hello binh.cao,

Thank you for the report. Looking over the behavior you've described, I suspect this may be due to the $push operator. Are you using the $position modifier for the $push operator? From the documentation for $push:

$position: Specifies the location in the array at which to insert the new elements. Requires the use of the $each modifier. Without the $position modifier, the $push appends the elements to the end of the array.

Thanks,
Mark

Comment by CAO Quang Binh [ 28/Jul/17 ]

UPDATE:
My expectation is: D, C, A, B

P/s: I don't know how to edit my description.

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