Array is reordered when using $lookup

user_1773265 0
Viewed 281

I have this aggregation:

db.getCollection("users").aggregate([
  {
    "$match": {
      "_id": "5a708a38e6a4078bd49f01d5"
    }
  },
  {
    "$lookup": {
      "from": "user-locations",
      "localField": "locations",
      "as": "locations",
      "foreignField": "_id"
    }
  }
])

It works well, but there is one small thing that I don't understand and I can't fix. In the query output, the locations array is reordered by ObjectId and I really need to keep the original order of data.

Here is how the locations array from the users collection looks like

'locations' : [
     ObjectId("5b55e9820b720a1a7cd19633"), 
     ObjectId("5a708a38e6a4078bd49ef13f")
],

And here is the result after the aggregation:

'locations' : [
     {
        '_id' : ObjectId("5a708a38e6a4078bd49ef13f"),
        'name': 'Location 2'
     },
     {
        '_id' : ObjectId("5b55e9820b720a1a7cd19633"),
        'name': 'Location 1'
     }
],

What am I missing here? I really have no idea how to proceed with this issue. Could you give me a push?

aggregation-framework mongodb lookup
2 Answers

From this closed bug 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.

Basically the way any Mongo query/pipeline works is that it returns documents in the order they were matched, meaning the "right" order is not guaranteed especially if there's indes usage involved.

What you should do is add a $sort stage as suggested, like so:

db.collection.aggregate([
  {
    "$match": {
      "_id": "5a708a38e6a4078bd49f01d5"
    }
  },
  {
    "$lookup": {
      "from": "user-locations",
      "let": {
        "locations": "$locations"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$setIsSubset": [
                [
                  "$_id"
                ],
                "$$locations"
              ]
            }
          }
        },
        {
          $sort: {
            _id: 1 // any other sort field you want.
          }
        }
      ],
      "as": "locations",
    }
  }
])

You can also keep the original $lookup syntax you're using and just $unwind, $sort and then $group to restore the structure.

user_107703700

$lookup does not guarantee order of result documents, you can try a approach to manage natural order of document,

  • $unwind deconstruct locations array and add auto index number will start from 0,
  • $lookup with locations
  • $set to select first element from locations
  • $sort by index field in ascending order
  • $group by _id and reconstruct locations array
db.users.aggregate([
  { $match: { _id: "5a708a38e6a4078bd49f01d5" } },
  {
    $unwind: {
      path: "$locations",
      includeArrayIndex: "index"
    }
  },
  {
    $lookup: {
      from: "user-locations",
      localField: "locations",
      foreignField: "_id",
      as: "locations"
    }
  },
  { $set: { locations: { $arrayElemAt: ["$locations", 0] } } },
  { $sort: { index: 1 } },
  {
    $group: {
      _id: "$_id",
      locations: { $push: "$locations" }
    }
  }
])

Playground

user_89871280
Related