Count nested and outer data

Question

I have the following mongo data structure:

[
  {
    _id: "......",
    libraryName: "a1",
    stages: [
      {
        _id: '....',
        type: 'b1',
      },
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b3',
      },
      {
        _id: '....',
        type: 'b1',
      },
    ],
  },
  {
    _id: "......",
    libraryName: "a1",
    stages: [
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b1',
      },
    ],
  },
    {
    _id: "......",
    libraryName: "a2",
    stages: [
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b2',
      },
      {
        _id: '....',
        type: 'b1',
      },
    ],
  },
]

Assume this is the Session collection. Now, each session document has some irrelevant _id and libraryName key. Furthermore, each document has array of stages documents. Each stage document has some irrelevant _id and type. I want to count 2 things.

First - I want to count for each libraryName, how many session objects it has. The solution for this query would be:

const services = await Session.aggregate(
    [
        {
            $group: {
                _id: "$libraryName",
                count: { $sum: 1 },
            },
        }
    ]
);

Second - I want, per libaryName to count for each stage type how many nested stages documents it has.

So the final result I wish to retrieve is:

[
{
    libraryName: 'a1',
    count: 456,
    stages: [
      {
        type: 'b1',
        count: 43,
      },
      {
        type: 'b2',
        count: 44,
      }
    ],
  },
  {
    libraryName: 'a2',
    count: 4546,
    stages: [
      {
        type: 'b1',
        count: 43
      },
      {
        type: 'b3',
        count: 44
      }
    ]
  }
]

Changed to:

 [
  {
    "_id": "a1",
    "count": 2,
    "stages": [
      {
        "count": 1,
        "type": "b3"
      },
      {
        "count": 3,
        "type": "b1"
      },
      {
        "count": 4,
        "type": "b2"
      }
    ]
  },
  {
    "_id": "a2",
    "count": 1,
    "stages": [
      {
        "count": 1,
        "type": "b1"
      },
      {
        "count": 3,
        "type": "b2"
      }
    ]
  }
]

user_4679320 · Answer

Using the sample data in the question post and the aggregation query:

db.collection.aggregate([
{ 
    $unwind: "$stages" 
},
{ 
    $group: {
        _id: { libraryName: "$libraryName", type: "$stages.type" }, 
        type_count: { "$sum": 1 } 
    } 
},
{ 
    $group: { 
        _id: { libraryName: "$_id.libraryName" }, 
        count: { "$sum": "$type_count" }, 
        stages: { $push: { type: "$_id.type", count: "$type_count" } }
    } 
},
{ 
    $project: { 
        libraryName: "$_id.libraryName", 
        count: 1, 
        stages: 1, 
        _id: 0  
    } 
}
])

I get the following results:

{
        "libraryName" : "a2",
        "count" : 4,
        "stages" : [
                {
                        "type" : "b1",
                        "count" : 1
                },
                {
                        "type" : "b2",
                        "count" : 3
                }
        ]
}
{
        "libraryName" : "a1",
        "count" : 8,
        "stages" : [
                {
                        "type" : "b3",
                        "count" : 1
                },
                {
                        "type" : "b1",
                        "count" : 3
                },
                {
                        "type" : "b2",
                        "count" : 4
                }
        ]
}

[ EDIT - ADD ] : This is an answer after the question post's expected result is modified. This query uses the question post's sample documents as input.

db.collection.aggregate([
{ 
    $group: { 
        _id: { libraryName: "$libraryName" }, 
        count: { "$sum": 1 },
        stages: { $push: "$stages" }
    } 
},
{ 
    $unwind: "$stages" 
},
{ 
    $unwind: "$stages" 
},
{ 
    $group: {
        _id: { libraryName: "$_id.libraryName", type: "$stages.type" }, 
        type_count: { "$sum": 1 },
        count: { $first: "$count" }
    } 
},
{ 
    $group: { 
        _id: "$_id.libraryName", 
        count: { $first: "$count" },
        stages: { $push: { type: "$_id.type", count: "$type_count" } }
    } 
},
])

The result:

{
        "_id" : "a2",
        "count" : 1,
        "stages" : [
                {
                        "type" : "b2",
                        "count" : 3
                },
                {
                        "type" : "b1",
                        "count" : 1
                }
        ]
}
{
        "_id" : "a1",
        "count" : 2,
        "stages" : [
                {
                        "type" : "b2",
                        "count" : 4
                },
                {
                        "type" : "b3",
                        "count" : 1
                },
                {
                        "type" : "b1",
                        "count" : 3
                }
        ]
}

Count nested and outer data

1 Answers