Given the following input:

[
  {
    "statuses": [
      {
        "status": "allowed",
        "count": 3,
        "events_count": [
          "2001",
          "1001",
          "1001"
        ]
      }
    ],
    "date": "2022-09-10 15:00",
    "_id": "2022-09-10 15:00"
  }
]

I need count the number of occurrences of stauses.events_count, so the output would be:

[
  {
    "statuses": [
      {
        "status": "allowed",
        "count": 3,
        "events_count": [
          {"type": "2001", "count": 1},
          {"type": "1001", "count": 2},
        ]
      }
    ],
    "date": "2022-09-10 15:00",
    "_id": "2022-09-10 15:00"
  }
]

What I've tried

This is what I got so far:

db.collection.aggregate([
  {
    "$unwind": "$statuses"
  },
  {
    "$unwind": "$statuses.events_count"
  },
  {
    "$group": {
      "_id": {
        "event_count": "$statuses.events_count",
        "status": "$statuses.status",
        "date": "$date",
        "count": "$statuses.count"
      },
      "occurences": {
        "$sum": 1
      }
    }
  }
])

Which produces:

[
  {
    "_id": {
      "count": 3,
      "date": "2022-09-10 15:00",
      "event_count": "2001",
      "status": "allowed"
    },
    "occurences": 1
  },
  {
    "_id": {
      "count": 3,
      "date": "2022-09-10 15:00",
      "event_count": "1001",
      "status": "allowed"
    },
    "occurences": 2
  }
]

I'm having difficulties grouping everything back together. I tried grouping by date and pushing back to a 'statuses' array, but it produces two items in the array (with status==allowed), rather than 1 item with status==allowed

CodePudding user response：

You did 2 $unwinds, so it should be 2 $groups in reverse order:

  {
    "$group": {
      "_id": {
        "status": "$_id.status",
        "count": "$_id.count",
        "date": "$_id.date"
      },
      "event_count": {
        "$push": {
          "type": "$_id.event_count",
          "count": "$occurences"
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.date",
      "date": {"$last": "$_id.date"},
      "statuses": {
        "$push": {
          "status": "$_id.status",
          "count": "$_id.count",
          "event_count": "$event_count"
        }
      }
    }
  }