6

I have the following dataset (categories):

[{
  "_id": 1,
  "name": "Root",
  "parent": null
},
 {
   "_id": 2,
   "name": "Sub - Level 1",
   "parent": 1
 }, {
   "_id": 3,
   "name": "Sub - Level 2",
   "parent": 2
 }, {
   "_id": 4,
   "name": "Sub - Level 3",
   "parent": 3
 }
]

and I am running the following pipeline on this dataset to fetch the tree recursively:

[{
  '$match': {
    '_id': 1
  }
}, {
  '$graphLookup': {
    'from': 'categories', 
    'startWith': '$_id', 
    'connectFromField': '_id', 
    'connectToField': 'parent', 
    'as': 'menu'
  }
}, {
    '$sort': {
      'menu.parent': 1
    }
  }]

The aim is to fetch the tree recursively like this:

{
    "_id": 1,
    "name": "Root",
    "parent: "null",
    "menu": [
        {..sub},{..sub},{...sub}
    ]
}

It does the job, but each time the query is executed the order of the elements in the menu array is different. EVERYTIME!

"menu" : [{... Sub - Level 2},{... Sub - Level 3}, {... Sub - Level 1}]
"menu" : [{... Sub - Level 1},{... Sub - Level 3}, {... Sub - Level 2}]
"menu" : [{... Sub - Level 3},{... Sub - Level 1}, {... Sub - Level 2}]

Is this the normal behaviour of $graphLookup or am I missing out on something? How am I supposed to sort the menu array?

saibbyweb
  • 2,864
  • 2
  • 27
  • 48

1 Answers1

7

Documents returned in the as field are not guaranteed to be in any order.

https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/#definition

If you need an order, consider specifying the 'depthField' argument, and sorting by that.

https://jira.mongodb.org/browse/SERVER-26153

Workaround:

db.categories.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$graphLookup": {
      "from": "categories",
      "startWith": "$_id",
      "connectFromField": "_id",
      "connectToField": "parent",
      "as": "menu",
      depthField: "order"
    }
  },
  {
    $unwind: "$menu"
  },
  {
    $sort: {
      _id: 1,
      "menu.order": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      parent: {
        $first: "$parent"
      },
      menu: {
        $push: "$menu"
      }
    }
  },
  {
    $unset: "menu.order" //Change to $project:{"menu.order":0} for MongoDB <v4.2
  }
])

MongoPlayground

Valijon
  • 12,667
  • 4
  • 34
  • 67
  • 1
    Thanks a lot for the answer. I just have one question, would it be better to use my pipeline and sort the menu array in JS rather than using these extra pipelines? What according to you would be faster and cost effective? – saibbyweb May 24 '20 at 20:15
  • 2
    @saibbyweb for small amount of data, JS / BSON order has the same performance. For huge dataset, BSON will have better performance – Valijon May 24 '20 at 21:34
  • Got to learn a lot from your answer. Thanks again, have a great day ahead. – saibbyweb May 25 '20 at 08:43
  • This is understated in docs imo @Valijon life saver – Eggcellentos Feb 21 '22 at 14:22