Mongodb query to sort answers based on the length of upvotes array?

Question

I am trying to write a Mongodb query to sort answers based on the length of upvotes array. The answers having more upvotes should come first. Can anyone please help me with the query?

Here is my collection object:

{
    "_id": {
        "$oid": "607fcba8a6b6db2830060a2b"
    },
    "userID": {
        "$oid": "60782e1d4709d515c0684c45"
    },
    "title": "que3",
    "description": "text message",
    "crowdID": "6042142f5a6e0d1d7e694534",
    "userType": "Professional",
    "answers": [
        {
            "_id": {
                "$oid": "607fcbb5a6b6db2830060a2d"
            },
            "userID": {
                "$oid": "60782e004709d515c0684c44"
            },
            "body": "que6",
            "comments": [],
            "date": {
                "$date": "2021-04-21T06:52:37.906Z"
            },
            "upvotes": []
        },
        {
            "_id": {
                "$oid": "607fcbb6a6b6db2830060a2f"
            },
            "userID": {
                "$oid": "60782e004709d515c0684c44"
            },
            "body": "que6",
            "comments": [],
            "date": {
                "$date": "2021-04-21T06:52:38.706Z"
            },
            "upvotes": [
                {
                    "_id": {
                        "$oid": "607fcbefa6b6db2830060a31"
                    },
                    "userID": {
                        "$oid": "607933e4d281292298c99025"
                    },
                    "date": {
                        "$date": "2021-04-21T06:53:35.115Z"
                    }
                },
                {
                    "_id": {
                        "$oid": "607fcc01a6b6db2830060a33"
                    },
                    "userID": {
                        "$oid": "60782e1d4709d515c0684c45"
                    },
                    "date": {
                        "$date": "2021-04-21T06:53:53.641Z"
                    }
                }
            ]
        },
        {
            "_id": {
                "$oid": "607fcc14a6b6db2830060a35"
            },
            "userID": {
                "$oid": "60782e004709d515c0684c44"
            },
            "body": "que6",
            "comments": [],
            "date": {
                "$date": "2021-04-21T06:54:12.492Z"
            },
            "upvotes": []
        },
        {
            "_id": {
                "$oid": "607fcc15a6b6db2830060a37"
            },
            "userID": {
                "$oid": "60782e004709d515c0684c44"
            },
            "body": "que6",
            "comments": [],
            "date": {
                "$date": "2021-04-21T06:54:13.149Z"
            },
            "upvotes": []
        }
    ],
    "date": {
        "$date": "2021-04-21T06:52:24.964Z"
    },
    "__v": 4

Answer 1

Finally after 4 days of research in mongo, I have created a query which is sorting nested data as I want:

db.Question.aggregate([  
    { "$match" : {  "_id": ObjectId(req.params.questionId) }} , 
    {
      $lookup: {
        from: "users",
        localField: "userID",
        foreignField: "_id",
        as: "user_data"
      }
    },
    { $unwind: { "path": "$answers", "preserveNullAndEmptyArrays": true } },
    {
      $lookup: {
        from: "users",
        localField: "answers.userID",
        foreignField: "_id",
        as: "answer_user_data"
      }
    },
    { $unwind: { "path": "$answers.comments", "preserveNullAndEmptyArrays": true } },
    {
      $lookup: {
        from: "users",
        localField: "answers.comments.userID",
        foreignField: "_id",
        as: "comment_user_data"
      }
    },
    {
      $project: {
        _id : 1,
        title : 1,
        description:1,
        crowdId: 1,
        date:1,
        user_data: { "$arrayElemAt": [ "$user_data", 0 ] },
        answers: 
        { 
          $cond: 
          { 
            if: { $gt : [ "$answers._id", 0 ]}, 
            then: [{
              _id: "$answers._id",
              body: "$answers.body",
              date: "$answers.date",
              upvotes_count: { $cond: { if: { $isArray: "$answers.upvotes" }, then: { $size: "$answers.upvotes" }, else: "0"} },
              user_data:{ "$arrayElemAt": [ "$answer_user_data", 0 ] },
              comments: 
              { 
                $cond: 
                { 
                  if: { $gt : [ "$answers.comments._id", 0 ]}, 
                  then: [{
                    _id: "$answers.comments._id",
                    body: "$answers.comments._id",
                    date: "$answers.comments._id",
                    user_data:{ "$arrayElemAt": [ "$comment_user_data", 0 ] },
                  }], 
                  else: [] 
                } 
              }
            }], 
            else: [] 
          } 
        }
      }
    },
    {
      $sort : {
        "answers.upvotes_count": -1,
        "answers.user_data.type": -1,
      }
    },
    {
      $group: {
          _id: '$_id',
          "title": { "$first": "$title" },
          "description": { "$first": "$description" },
          "crowdId": { "$first": "$crowdId" },
          "date": { "$first": "$date" },
          "user_data": { "$first": "$user_data" },
          "answers": { "$push": { $first : "$answers"} },
      }
    }        
      
  ])

The above query will sort my nested answers based on the upvotes in each answers object as well as fetch nested user data from referenced nested "userId" in questions, answers and comments.

Sorting is based on the number of votes and user type. If answers having same number of upvotes then user type "Professional" answer will be on the top.