Count nested items conditionally in an aggregation-CodePudding

I am trying to count status occurrences in a MongoDB collection from two places. Given document like the one below, I would like to count status from the root of the document if there are no statues under statuses in one aggregation, but haven't been able to determine how to do this conditionally.

{
    "id": "some unique id",
    "groupId": "some many to one id mapping",
    ...
    "status": "ACCEPTED",
    "statuses": [{
        "personId": "some id",
        "status": "ACCEPTED"
    }, {
    "personId": "some other person",
        "status": "REMOVED"
    }]
}

Right now I have an aggregation to count all statuses.status but haven't been able to get accurate counts if there are no statues. This may require a model change, but ideally I would think there is a method to do this conditionally.

My aggregation pipeline looks like this:

final List<DBObject> statusPipeline = Aggregation.newAggregation(
                        match,
                        Aggregation.group("groupId", "statuses.personId", "statuses.status"),
                        Aggregation.unwind("status"),
                        Aggregation.group("$groupId", "status").count().as("total")
                )
                .toPipeline(Aggregation.DEFAULT_CONTEXT).stream()
                .map(BasicDBObject::new)
                .collect(Collectors.toList());

An example of a document that I want to include in the aggregate:

{
        "id": "some unique id",
        "groupId": "some many to one id mapping",
        ...
        "status": "UNPROVISIONED",
        "statuses": []
}

The final counts for these two documents should be UNPROVISIONED: 1, ACCEPTED: 1, and REMOVED: 1. Or something like:

[{
   "groupId": "A",
   "UNPROVISIONED": 1
}, {
   "groupId": "A",
   "ACCEPTED": 1
}, {
   "groupId": "A",
   "REMOVED": 1,
}]

CodePudding user response：

I'm not sure I fully understand the grouping/counting logic, but perhaps this provides the output you are looking for.

db.collection.aggregate([
  {
    "$set": {
      "statuses": {
        "$cond": [
          { // if there are statuses.status ...
            "$gt": [{"$size": "$statuses.status"}, 0]
          },
          // ... use those
          "$statuses.status",
          // ... otherwise use status
          ["$status"]
        ]
      }
    }
  },
  {
    "$unwind": "$statuses"
  },
  {
    "$group": {
      "_id": {
        "groupId": "$groupId",
        "status": "$statuses"
      },
      "count": {"$count": {}}
    }
  }
])

Try it on mongoplayground.net.