CODEWITHSUNDEEP
mongodbsortingmongooseaggregation-frameworklookup
Marcel Streicher
Marcel Streicher

Reputation: 63

Mongodb aggregation lookup array, is not sorting correctly

My MongoDB collection have a field with an array of ObjectId. This array contains the order of the ObjectIds may appears on the list. It happens then I trying to use $lookup in aggregation, it's sorting my array by the creation order.

When I use just:

$project: {
                list: 1
            }

the result is the correct order of the elements:

[
  5e9b7dc0500c9a5c3809b0c8,
  5e9b7efad230686164a64bdb,
  5e9b7dfc6244792818f7cdba,
  5e9b7e355a7c843e90a2ed48,
  5e9b7e86efb9111d1c91ea77,
  5e9b7eb26463144eac64cb16
]

and when I use lookup the order change:

$lookup: { 
        from: 'users',
        localField: 'user',
        foreignField: '_id',
        as: 'list'
    }
},
{
    $project: {
        list: 1
    }
}

it's the result

[
{_id: 5e9b7dc0500c9a5c3809b0c8 },
{_id: 5e9b7dfc6244792818f7cdba },
{_id: 5e9b7e355a7c843e90a2ed48 },
{_id: 5e9b7e86efb9111d1c91ea77 },
{_id: 5e9b7eb26463144eac64cb16 },
{_id: 5e9b7efad230686164a64bdb }
]

Could anybody help me with this?

edit:

Using findOne().populate() instead of aggregate() was the easier alternative to me in this case.

Upvotes: 0

Views: 562

Answers (1)

Joe
Joe

Reputation: 28316

The $lookup stage returns the fields in the order they are encountered while querying the from collection. Since the foreignField is _id, the query executor will likely use the {_id:1} index to lookup those documents, which means they will be encounter in the order they appear in that index, i.e. sorted by _id. Since the first bits of an ObjectId are the epoch time that the id was created, that will give them to you in creation order.

If you need the fields to be in a specific order, you will need to either add a field to the looked up documents that you can sort on, or $unwind the array before lookup so each lookup returns a single document, and $group the results back into an array afterward.

Upvotes: 1

Related Questions