MongoDB : sort Multiple sub document

Question

I am trying to sort my mongo collection in ascending order. But after executing my code, the result is not in the sorted order.

My Mongo collection is

{
"_id" : ObjectId("5e60e61450c82e1abc7c82bd"),
"barId" : "5e55f10d8b54541278e9cb07",
"juices" : [ 
    {
        "_id" : 1,
        "mixerId" : 1,
        "mixerName" : "Tejuino",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 2,
        "mixerId" : 1,
        "mixerName" : "Izze",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 3,
        "mixerId" : 1,
        "mixerName" : "Kaffir lime",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 1,
        "mixerName" : "Limeade",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 5,
        "mixerId" : 1,
        "mixerName" : "Must",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 6,
        "mixerId" : 1,
        "mixerName" : "Pog",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 7,
        "mixerId" : 1,
        "mixerName" : "Petimezi",
        "price" : 0,
        "isActive" : 0
    }
],
"soda" : [ 
    {
        "_id" : 1,
        "mixerId" : 2,
        "mixerName" : "REGULAR SODA",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 2,
        "mixerId" : 2,
        "mixerName" : "Fanta Grape",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 3,
        "mixerId" : 2,
        "mixerName" : "Stewart's Black Cherry Wishniak",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 2,
        "mixerName" : "Dr. Brown's Black Cherry",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 5,
        "mixerId" : 2,
        "mixerName" : "A&W Cream",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 6,
        "mixerId" : 2,
        "mixerName" : "Mug Cream",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 7,
        "mixerId" : 2,
        "mixerName" : "A&W Root Beer",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 8,
        "mixerId" : 2,
        "mixerName" : "Mountain Dew",
        "price" : 0,
        "isActive" : 0
    }
],
"others" : [ 
    {
        "_id" : 1,
        "mixerId" : 3,
        "mixerName" : "GIN & BITTER LEMON",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 2,
        "mixerId" : 3,
        "mixerName" : "GIN & GINGER",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 3,
        "mixerId" : 3,
        "mixerName" : "GIN & VERMOUTH",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 3,
        "mixerName" : "GIN & LIME CORDIAL",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 5,
        "mixerId" : 3,
        "mixerName" : "GIN & GRAPEFRUIT",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 6,
        "mixerId" : 3,
        "mixerName" : "GIN & PINK LEMONADE",
        "price" : 0,
        "isActive" : 0
    }
]
}

I want to sort my sub document as ascending based on mixerName. The below is my mongodb query

  db.getCollection('mixeritems').aggregate([
            { $match: { barId: '5e55f10d8b54541278e9cb07' }   },
            { $project: {
                juices: { $filter: {
                        input: "$juices",
                        as: "juices",
                        cond: { $eq: [ "$$juices.isActive", 1 ] }
                        } },
                soda: { $filter: {
                        input: "$soda",
                        as: "soda",
                        cond: { $eq: [ "$$soda.isActive", 1 ] }
                        } } ,       
                others: { $filter: {
                        input: "$others",
                        as: "others",
                        cond: { $eq: [ "$$others.isActive", 1 ] }
                        } }           
            },},
                            { $unwind: '$juices'},{ $unwind: '$soda'},{ $unwind: '$others'},
            { $sort : { 'juices.mixerName' : 1,"soda.mixerName" : 1,"others.mixerName" : 1 } },
            {$group:{
    _id: '$_id',
   juices: { $addToSet: "$juices"},
   soda: { $addToSet: "$soda"},
   others: { $addToSet: "$others"}
    }
}])

After excuting this query , the result I got is given below:

  {
"_id" : ObjectId("5e60e61450c82e1abc7c82bd"),
"juices" : [ 
    {
        "_id" : 5,
        "mixerId" : 1,
        "mixerName" : "Must",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 1,
        "mixerName" : "Limeade",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 3,
        "mixerId" : 1,
        "mixerName" : "Kaffir lime",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 6,
        "mixerId" : 1,
        "mixerName" : "Pog",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 1,
        "mixerId" : 1,
        "mixerName" : "Tejuino",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 2,
        "mixerId" : 1,
        "mixerName" : "Izze",
        "price" : 0,
        "isActive" : 1
    }
],
"soda" : [ 
    {
        "_id" : 2,
        "mixerId" : 2,
        "mixerName" : "Fanta Grape",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 5,
        "mixerId" : 2,
        "mixerName" : "A&W Cream",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 6,
        "mixerId" : 2,
        "mixerName" : "Mug Cream",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 1,
        "mixerId" : 2,
        "mixerName" : "REGULAR SODA",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 3,
        "mixerId" : 2,
        "mixerName" : "Stewart's Black Cherry Wishniak",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 7,
        "mixerId" : 2,
        "mixerName" : "A&W Root Beer",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 2,
        "mixerName" : "Dr. Brown's Black Cherry",
        "price" : 0,
        "isActive" : 1
    }
],
"others" : [ 
    {
        "_id" : 3,
        "mixerId" : 3,
        "mixerName" : "GIN & VERMOUTH",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 2,
        "mixerId" : 3,
        "mixerName" : "GIN & GINGER",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 4,
        "mixerId" : 3,
        "mixerName" : "GIN & LIME CORDIAL",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 5,
        "mixerId" : 3,
        "mixerName" : "GIN & GRAPEFRUIT",
        "price" : 0,
        "isActive" : 1
    }, 
    {
        "_id" : 1,
        "mixerId" : 3,
        "mixerName" : "GIN & BITTER LEMON",
        "price" : 0,
        "isActive" : 1
     }
   ]
 }

Please Help me.

Welcome to the stackoverflow. Congratulations for asking a clear question in your first question. — SuleymanSah
– SuleymanSah, Commented Mar 11, 2020 at 11:20

SuleymanSah · Accepted Answer · 2020-03-11 11:19:18Z

I made it work by sorting inner arrays seperately. The most beautiful way of sorting inner array is this answer.

unwind -> sort -> group -> replaceRoot

So all we need to do is repeat this logic there times for juices, soda and others arrays.

Playground

db.getCollection('mixeritems').aggregate([
  {
    $match: {
      barId: "5e55f10d8b54541278e9cb07"
    }
  },
  {
    $project: {
      juices: {
        $filter: {
          input: "$juices",
          as: "juices",
          cond: {
            $eq: [
              "$$juices.isActive",
              1
            ]
          }
        }
      },
      soda: {
        $filter: {
          input: "$soda",
          as: "soda",
          cond: {
            $eq: [
              "$$soda.isActive",
              1
            ]
          }
        }
      },
      others: {
        $filter: {
          input: "$others",
          as: "others",
          cond: {
            $eq: [
              "$$others.isActive",
              1
            ]
          }
        }
      }
    }
  },
  {
    $unwind: "$juices"
  },
  {
    $sort: {
      "juices.mixerName": 1
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "juices": {
        "$push": "$juices"
      },
      "doc": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$doc",
          {
            "juices": "$juices"
          }
        ]
      }
    }
  },
  {
    $unwind: "$soda"
  },
  {
    $sort: {
      "soda.mixerName": 1
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "soda": {
        "$push": "$soda"
      },
      "doc": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$doc",
          {
            "soda": "$soda"
          }
        ]
      }
    }
  },
  {
    $unwind: "$others"
  },
  {
    $sort: {
      "others.mixerName": 1
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "others": {
        "$push": "$others"
      },
      "doc": {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$doc",
          {
            "others": "$others"
          }
        ]
      }
    }
  }
])

@deepu you have know more than 15 reputations, it means you can upvote the answers, I appreciate if you can.

Collectives™ on Stack Overflow

MongoDB : sort Multiple sub document

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related