Aggregation queries on nested array of objects in mongodb

Question

I have following collection. each document may be represented as following. there can be multiple customer referees for each document.

{
  _id:objectId(""),
  customerId: "Some Id",
  name: "Customer name",
  customerReferee: [
   {
     status: 'COMPLETED',
     name: "Referee name1"
   },
   {
     status: 'PENDING',
     name: "Referee name2"
   },
  ],
}

I would like to have top 20 documents who has maximum number of 'COMPLETED' customerReferees.

your question is not clear, 20 documents who has maximum number of 'COMPLETED' => describe maximum means how many? — turivishal
– turivishal, Commented May 25, 2021 at 14:54
There can be any number of COMPLETED referees. I want to order documents in descending order where document which has most number of COMPLETED referees will be the first and later the document which has lesser number of referees than first one. I want first 20 of such documents. @turivishal — hrushilok
– hrushilok, Commented May 26, 2021 at 3:24

turivishal · Accepted Answer · 2021-05-26 05:05:57Z

1

$match status condition
$filter to iterate loop of customerReferee array nad filter by status
$size to get total elements in above filter result
$sort by total element that we have added a field customerRefereeSize in descending order
$skip to start cursor from 0
$limit to get first 20 elements

db.collection.aggregate([
  { $match: { "customerReferee.status": "COMPLETED" } },
  {
    $addFields: {
      customerRefereeSize: {
        $size: {
          $filter: {
            input: "$customerReferee",
            cond: { $eq: ["$$this.status", "COMPLETED"] }
          }
        }
      }
    }
  },
  { $sort: { customerRefereeSize: -1 } },
  { $skip: 0 },
  { $limit: 20 }
])

Playground

edited May 26, 2021 at 5:05

answered May 26, 2021 at 4:17

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hrushilok Over a year ago

Thanks @turivishal . Its working in playground but In my database its throwing '"Unrecognized pipeline stage name: '$set'", "code" : 40324,' this error. What might be the cause?

turivishal Over a year ago

i am not sure but try $addFields instead of $set, let me know it is working or not.

hrushilok Over a year ago

Thanks for update. Now the error is gone and it's working fine. ✌🏼😊

Collectives™ on Stack Overflow

Aggregation queries on nested array of objects in mongodb

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related