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

Can be done with an aggregation. Similar question is asked here: https://stackoverflow.com/questions/17988585/do-sort-works-for-sub-array-document — fnyger, Apr 21 '21 at 09:21
Does this answer your question? [Do $sort works for sub array document](https://stackoverflow.com/questions/17988585/do-sort-works-for-sub-array-document) — Tom Slabbaert, Apr 21 '21 at 09:40

score 0 · Accepted Answer · answered Apr 28 '21 at 06:00

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.

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

1 Answers1