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}
  }
})

CodePudding user response：

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:

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

Playground