MongoDB Multi level $lookup sort not working

Question

Multi level $lookup sort not working in aggregation.

Sorting works only for country, state name. Tried applying sorting for cities, but country sort overwrites the city sort.

Query2 is working but i don't want to sort collections inside lookup pipeline.

Is there any way to achieve all level of sorting(country,state,city) in Query1

Query1(Not Working):

Country.aggregate([
            {
                $lookup:{
                    from: 'states',
                    localField:'_id',
                    foreignField:'countryId',
                    as:'states'
                }
            },
            {
                $unwind: {
                    path: "$states",
                    preserveNullAndEmptyArrays: true
                }
            },
            {
                $sort:  {
                    'states.name': 1
                }
            },
            {
                $lookup:{
                    from: 'cities',
                    localField:'states._id',
                    foreignField:'stateId',
                    as:'states.cities'
                }
            },
            {
                $sort:  {
                    'states.cities.name': 1
                }
            },
            {
                $group: {
                    _id: {
                        _id: '$_id',
                        name: '$name'
                    },
                    states: {
                        $push: '$states'
                    }
                }
            },
            {
                $project: {
                    _id: '$_id._id',
                    name: '$_id.name',
                    states: 1
                }
            }, 
            {
                $sort:  {
                    name: 1
                }
            }
        ])

Query2(Working): Execution time is 8 times higher than Query1.

[
        {
            $lookup : {
                from : 'states',
                let: { 'countryId': '$_id' },
                pipeline: [
                    {
                        $match: {
                            $expr:
                                {
                                    $eq: ['$countryId', '$$countryId']
                                }
                            }
                        },
                    {
                        $sort : {
                            name : -1
                        }
                    }
                ],
                as : 'states'
            }
        },
        {
            $unwind: {
                path: '$states',
                preserveNullAndEmptyArrays: true
            }
        },
        {
            $lookup : {
                from : 'cities',
                let: { 'stateId': '$states._id' },
                pipeline: [
                    {
                        $match: {
                            $expr:
                                {
                                    $eq: ['$stateId', '$$stateId']
                                }
                            }
                        },
                    {
                        $sort : {
                            name : -1
                        }
                    }
                ],
                as : 'states.cities'
            }
        },
        {
            $group: {
                _id: {
                    _id: '$_id',
                    name: '$name'
                },
                states: {
                    $push: '$states'
                }
            }
        },
        {
            $project: {
                _id: '$_id._id',
                name: '$_id.name',
                states: 1
            }
        }
    ]

Answer 1

In the newer $lookup syntax you do not need to use $unwind to join nested fields. You can easily use $lookup inside the pipeline to join multiple level.

[
  { "$lookup": {
    "from": "states",
    "let": { "countryId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$countryId", "$$countryId"] }}},
      { "$lookup": {
        "from": "cities",
        "let": { "stateId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$stateId", "$$stateId"] }}},
          { "$sort": { "name": -1 }}
        ],
        "as": "cities"
      }},
      { "$sort": { "name": -1 }}
    ],
    "as": "states"
  }}
]