CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
podeig
podeig

Reputation: 2741

How to sort by mutiple fields with conditons in MongoDB

Need help to sort these documents:

      const docs = Docs.find(
          { 
            'publishedOn.profileId': groupProfile._id,
          }, 
          { sort: { ??? }}
      );

I need to find documents which has defined 'publishedOn.profileId' and sort by 'awards.type' = 'challengeWinner' and by its 'awards.score' Not all document has awards.type = 'challengeWinner'. I need to take on the top 'awards.score' = 1, then 2, then 3 and then the rest by 'writtenDate'. I have no idea how to fix it. Is it possible?

[
{
    "_id" : "5FW9EDW8gi3M8R7XK",
    "createdAt" : ISODate("2021-06-13T00:11:48.638Z"),
    "title" : "My solution",
    "writtenDateType" : 4,
    "writtenDate" : ISODate("2021-06-13T00:00:00.000Z"),
    "userId" : "dC35hwe6XMRhvqWBv",
    "publishedOn" : [ 
        {
            "profileId" : "36oPw2zxYCpKxfiu2",
            "publishedDate" : ISODate("2021-06-13T00:11:48.787Z"),
            "userId" : "dC35hwe6XMRhvqWBv"
        }, 
        {
            "profileId" : "9y2RwJpzzyk29ApiC",
            "userId" : "dC35hwe6XMRhvqWBv",
            "publishedDate" : ISODate("2021-06-13T00:16:01.529Z")
        }
    ],
    "awards" : [ 
        {
            "type" : "topPoem",
            "score" : 5,
            "addedAt" : ISODate("2021-06-24T23:04:10.454Z"),
            "updatedAt" : ISODate("2021-06-25T23:30:00.069Z")
        },
        {
            "type" : "challengeWinner",
            "score" : 2,
            "challengeId" : "9y2RwJpzzyk29ApiC",
            "addedAt" : ISODate("2021-06-24T23:04:10.454Z"),
            "updatedAt" : ISODate("2021-06-25T23:30:00.069Z")
        }
    ]
},
{
    "_id" : "upzvo8BeHyQ9r9Yfv",
    "createdAt" : ISODate("2021-06-19T15:35:13.716Z"),
    "title" : "Briches",
    "writtenDateType" : 2,
    "writtenDate" : ISODate("2003-01-01T00:00:00.000Z"),
    "userId" : "A32228XMuZqxFe4Kz",
    "publishedOn" : [ 
        {
            "profileId" : "MLGkCtNyZ64bGKedG",
            "publishedDate" : ISODate("2021-06-19T15:35:13.861Z"),
            "userId" : "A32228XMuZqxFe4Kz"
        }, 
        {
            "profileId" : "9y2RwJpzzyk29ApiC",
            "userId" : "A32228XMuZqxFe4Kz",
            "publishedDate" : ISODate("2021-06-19T15:35:36.280Z")
        }
    ],
    "awards" : [ 
        {
            "type" : "challengeWinner",
            "score" : 1,
            "challengeId" : "9y2RwJpzzyk29ApiC",
            "addedAt" : ISODate("2021-06-24T22:59:00.948Z"),
            "updatedAt" : ISODate("2021-06-25T23:30:00.067Z"),
            "claps" : 19,
            "clapsUsers" : 4
        }, 
        {
            "type" : "suggestedHomepage",
            "score" : 1,
            "addedAt" : ISODate("2021-06-24T22:59:59.981Z"),
            "updatedAt" : ISODate("2021-06-24T22:59:59.981Z")
        }
    ]
}
]

Upvotes: 0

Views: 38

Answers (1)

Arun AK
Arun AK

Reputation: 4370

I just learned and tried to solve your problem. I used aggregate to do the filter in your data.

  1. First I selected all the items which $match the `publishedOn.profileId".
  2. Then, I $project(ed) the items that are needed. In this case, I took the writtenDate and the matching awards.
  3. In order to choose the needed value from awards, I $filter (ed) the award type.
  4. Last, I did $sort for the award score first and then writtenDate,

db.collection.aggregate([
  {
    "$match": {
      "publishedOn.profileId": "9y2RwJpzzyk29ApiC"
    }
  },
  {
    "$project": {
      "writtenDate": 1,
      "awards": {
        "$filter": {
          "input": "$awards",
          "as": "award",
          "cond": {
            "$eq": [
              "$$award.type",
              "challengeWinner"
            ]
          }
        }
      }
    }
  },
  {
    "$sort": {
      "awards.score": 1,
      "writtenDate": 1,
    }
  }
])

Working of above query: https://mongoplayground.net/p/MzWQCR2Gshg

Happy Coding !!!

Upvotes: 1

Related Questions