Mongo aggregate on array objects using count

Question

I have a collection with documents in below format: (shown below 2 sample document)

1st doc:

{
  "date": 20221101,
  "time":1500,
  "productCode": "toycar",
  "purchaseHistory": [
    {
      "clientid": 123,
      "status": "SUCCESS"
    },
    {
      "clientid": 456,
      "status": "FAILURE"
    }
  ]
}

2nd doc:

{
  "date": 20221101,
  "time": 1500,
  "productCode": "toycar",
  "purchaseHistory": [
    {
      "clientid": 890,
      "status": "SUCCESS"
    },
    {
      "clientid": 678,
      "status": "SUCCESS"
    }
  ]
}

I want to query above and print output in below format where purchaseHistory.status = 'SUCCESS' and date = 20221101:

{productCode:"toycar", "time": 1500, "docCount": 2, "purchaseHistCount":3}

How can I achieve this? I tried below:

db.products.aggregate({
  $match : {date:20221101, 'purchaseHistory.status':'SUCCESS'},
  "$group": {
    "_id": {
      "pc": "$productCode",
      "time": "$time"
    },
    "docCount": {$sum :1}
  }
})

R2D2 · Accepted Answer · 2022-10-31 21:25:40Z

2

Something like this maybe:

db.collection.aggregate([
{
 $match: {
  date: 20221101,
  "purchaseHistory.status": "SUCCESS"
}
},
{
"$addFields": {
  "purchaseHistory": {
    "$filter": {
      "input": "$purchaseHistory",
      "as": "ph",
      "cond": {
        $eq: [
          "$$ph.status",
          "SUCCESS"
        ]
      }
    }
   }
  }
 },
 {
   $group: {
       _id: {
          t: "$time",
         pc: "$productCode"
      },
       docCount: {
          $sum: 1
      },
       purchaseHistCount: {
        $sum: {
          $size: "$purchaseHistory"
       }
      }
     }
   }
  ])

Explained:

Filter the matched documents.
Filter the purchaseHistory SUCCESS only.
Group the result to see count of matching documents & matching purchaseHistory.

Playground

edited Oct 31, 2022 at 21:25

answered Oct 31, 2022 at 21:13

R2D2

10.9k2 gold badges20 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Mongo aggregate on array objects using count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related