Aggregate and group the child list with the count

[AlexMan]

I have a category Document data like as shown below

[
  {
    "account": "Cat12",
    "activities": [
      {
        "name": "A1",
        "status": "S1",
        "type": "T1"
      },
      {
        "name": "A2",
        "status": "S2",
        "type": "T2"
      }
    ]
  },
  {
    "account": "Cat12",
    "activities": [
      {
        "name": "A3",
        "status": "S3",
        "type": "T3"
      },
      {
        "name": "A2",
        "status": "S2",
        "type": "T2"
      }
    ]
  },
  {
    "account": "Cat12",
    "activities": [
      {
        "name": "A1",
        "status": "S1",
        "type": "T1"
      },
      {
        "name": "A2",
        "status": "S2",
        "type": "T2"
      }
    ]
  },
  {
    "account": "Cat13",
    "activities": [
      {
        "name": "A1",
        "status": "S1",
        "type": "T1"
      },
      {
        "name": "A2",
        "status": "S2",
        "type": "T2"
      }
    ]
  }
]

What I am trying to do is to aggregate the activities based on the account. Lets say for Cat12 I should get the following output with the no repeated counts

[
  {
    "name": "A1",
    "status": "S1",
    "type": "T1",
    "count": 2
  },
  {
    "name": "A2",
    "status": "S2",
    "type": "T2",
    "count": 3
  },
  {
    "name": "A3",
    "status": "S3",
    "type": "T3",
    "count": 1
  }
]

and for Cat13 I should get like as shown below

[
  {
    "name": "A1",
    "status": "S1",
    "type": "T1",
    "count": 1
  },
 {
    "name": "A2",
    "status": "S2",
    "type": "T2",
    "count": 1
  }
]

Is this achievable using aggregate

[MaBeuLux88 (Maxime Beugnet)]

Hi @AlexMan and welcome in the MongoDB Community !

It’s a relatively basic aggregation pipeline. You can learn more about the Aggregation Pipeline in the dedicated course that we offer on MongoDB University in the course M121.

Here is my solution:

[
  {
    '$match': {
      'account': 'Cat12'
    }
  }, {
    '$unwind': {
      'path': '$activities'
    }
  }, {
    '$group': {
      '_id': {
        'name': '$activities.name', 
        'status': '$activities.status', 
        'type': '$activities.type'
      }, 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$project': {
      'name': '$_id.name', 
      'status': '$_id.status', 
      'type': '$_id.type', 
      'count': 1, 
      '_id': 0
    }
  }
]

Result for Cat12:

[
  { count: 2, name: 'A1', status: 'S1', type: 'T1' },
  { count: 3, name: 'A2', status: 'S2', type: 'T2' },
  { count: 1, name: 'A3', status: 'S3', type: 'T3' }
]

Results for Cat13:

[
  { count: 1, name: 'A1', status: 'S1', type: 'T1' },
  { count: 1, name: 'A2', status: 'S2', type: 'T2' }
]

Note that you need an index on {account:1} to support the $match stage.

Also, nothing prevents you from computing Cat12 and Cat13 at the same time:

[
  {
    '$unwind': {
      'path': '$activities'
    }
  }, {
    '$group': {
      '_id': {
        'cat': '$account', 
        'name': '$activities.name', 
        'status': '$activities.status', 
        'type': '$activities.type'
      }, 
      'count': {
        '$sum': 1
      }
    }
  }, {
    '$project': {
      'cat': '$_id.cat', 
      'name': '$_id.name', 
      'status': '$_id.status', 
      'type': '$_id.type', 
      'count': 1, 
      '_id': 0
    }
  }
]

Results:

[
  { count: 3, cat: 'Cat12', name: 'A2', status: 'S2', type: 'T2' },
  { count: 1, cat: 'Cat12', name: 'A3', status: 'S3', type: 'T3' },
  { count: 1, cat: 'Cat13', name: 'A1', status: 'S1', type: 'T1' },
  { count: 2, cat: 'Cat12', name: 'A1', status: 'S1', type: 'T1' },
  { count: 1, cat: 'Cat13', name: 'A2', status: 'S2', type: 'T2' }
]

Cheers,
Maxime.