Mongodb: Build category url with $graphLookup, $reduce & $concat in the right order

Question

What i try to archive is to get a URL in the right order as the parents of an document.

I generate this query:

Input

use('testdb');

db.categories.drop();

db.categories.insertMany([
  { '_id' : 'shoes', 'name' : 'Shoes', 'path' : 'shoes', 'parent': null},
  { '_id' : 'sportshoes', 'name' : 'Sport-Shoes', 'path' : 'sportshoes', 'parent': 'shoes'},
  { '_id' : 'sportshoes-black', 'name' : 'Sportshoes Black', 'path' : 'sportshoes-black', 'parent': 'sportshoes'},
  { '_id' : 'sportshoes-black-m', 'name' : 'Sportshoes Black M', 'path' : 'sportshoes-black-m', 'parent': 'sportshoes-black'},
  { '_id' : 'sportshoes-black-w', 'name' : 'Sportshoes Black W', 'path' : 'sportshoes-black-w', 'parent': 'sportshoes-black'},
]);


db.categories.aggregate( [
  {$match: { _id: 'sportshoes-black-m' } },
   {
      $graphLookup: {
         from: "categories",
         startWith: "$parent",
         connectFromField: "parent",
         connectToField: "_id",
         as: "url"
      }
   },
   {
      $project: {
        "name": 1,
        "url" : {
          $reduce: {
            input: "$url.path",
            initialValue: "",
            in: {
              '$concat': [
              '$$value',
              {'$cond': [{'$eq': ['$$value', '']}, '', '/']}, '$$this']
              }
            }
          }
        }
      }
    ]
  )

Output

[
  {
    _id: 'sportshoes-black-m',
    name: 'Sportshoes Black M',
    url: 'shoes/sportshoes/sportshoes-black'
  }
]

For this example it is the right order but when i try to fetch sportshoes-black-w i get it in the wrong order:

Output

[
  {
    _id: 'sportshoes-black-w',
    name: 'Sportshoes Black W',
    url: 'sportshoes-black/sportshoes/shoes'
  }
]

Answer 1

All you have to do is sort the urls before, If by any chance your using Mongo v4.4, the $function was introduced, this operator allows applying a custom javascript function to implement behaviour not supported by the MongoDB Query Language such as sorting an array like so:

db.categories.aggregate([
    {$match: {_id: 'sportshoes-black-m'}},
    {
        $graphLookup: {
            from: "categories",
            startWith: "$parent",
            connectFromField: "parent",
            connectToField: "_id",
            as: "url"
        }
    },
    {
        $addFields: {
            "url": {
                $function: {
                    body: function (urls) {
                        urls.sort((a, b) => a.path > b.path);
                        return urls;
                    },
                    args: ["$url"],
                    lang: "js"
                }
            }
        }
    },
    {
        $project: {
            "name": 1,
            "url": {
                $reduce: {
                    input: "$url.path",
                    initialValue: "",
                    in: {
                        '$concat': [
                            '$$value',
                            {'$cond': [{'$eq': ['$$value', '']}, '', '/']}, '$$this']
                    }
                }
            }
        }
    }
])

Sadly for previous Mongo version such capabilities do not exist, you'll have to $unwind, $sort and then $group to restore the structure you want, for obvious reasons this is very expensive so in that case I recommend you just do the sorting in code post aggregation.