MongoDB .NET Driver - Aggregate group and count

Question

I'm working on an app in C# that communicates with MongoDB. I'd like to show some statistics about the number of boxes in warehouses. Here is the Boxes collection:

{
    "content":"0",
    "warehouseId":"w0"
},
{
    "content":"0",
    "warehouseId":"w0"
},
{
    "content":"1",
    "warehouseId":"w0"
},
{
    "content":"1",
    "warehouseId":"w0"
},
{
    "content":"2",
    "warehouseId":"w0"
},
{
    "content":"0",
    "warehouseId":"w1"
}

There are more fields, but these are the ones I need now. The "content" is the id of an item from an Items collection and the "warehouseId" is from a Warehouses collection.

I'd like to show how many boxes there are of each item in each warehouse. It should look similar to this:

Warehouse: w0
0: 2
1: 2
2: 1

Warehouse: w1
0: 1
1: 0
2: 0

What I tried until now is this:

[{
    $group: {
        _id: {
            warehouseId: '$warehouseId',
            content: '$content'
        },
        boxes: {
            $count: {}
        }
    }
}, {
    $addFields: {
        warehouseId: '$_id.warehouseId',
        content: '$_id.content'
    }
}, {
    $project: {
        _id: 0
    }
}]

But this only gives me an output where I have a separate document for every item and I'm stuck here. How could I get the desired output and how do I resolve this in C#? I'm using MongoDB .NET Driver.

Answer 1

1. $group - Group by warehouseId and content fields. Perform count.

2. $group - Group by warehouseId. Push the document with k and v properties into boxes array.

3. $replaceRoot - Replace the input documents.

   3.1. $mergeObjects - Merge the documents with Warehouse field and the result from 3.1.1.

   3.1.1. $arrayToObject - Convert the box array to key-value pair.

4. $sort (Optional) - Order by Warehouse.

MongoDB query

db.box.aggregate([
  {
    $group: {
      _id: {
        warehouseId: "$warehouseId",
        content: "$content"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.warehouseId",
      boxes: {
        $push: {
          k: "$_id.content",
          v: "$count"
        }
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            Warehouse: "$_id"
          },
          {
            $arrayToObject: "$boxes"
          }
        ]
      }
    }
  },
  {
    $sort: {
      Warehouse: 1
    }
  }
])

Demo @ Mongo Playground

MongoDB .NET Driver syntax

PipelineStageDefinition<Box, BsonDocument> firstStage
    = PipelineStageDefinitionBuilder.Group<Box, BsonDocument>(new BsonDocument
    {
        { "_id", new BsonDocument
            {
                { "warehouseId", "$warehouseId" },
                { "content", "$content" }
            }
        },
        { "count", new BsonDocument
            {
                { "$sum", 1 }
            }
        }
    });

PipelineStageDefinition<BsonDocument, BsonDocument> secondStage
    = PipelineStageDefinitionBuilder.Group<BsonDocument, BsonDocument>(new BsonDocument
    {
        { "_id", "$_id.warehouseId" },
        { "boxes", new BsonDocument
            {
                { "$push", new BsonDocument
                    {
                        { "k", "$_id.content" },
                        { "v", "$count" }
                    }
                }
            }
        }
    });

PipelineStageDefinition<BsonDocument, BsonDocument> thirdStage 
    = PipelineStageDefinitionBuilder.ReplaceRoot<BsonDocument, BsonDocument>(new BsonDocument
        {
            { "$mergeObjects", new BsonArray
                {
                    new BsonDocument("Warehouse", "$_id"),
                    new BsonDocument("$arrayToObject", "$boxes")
                }
            }
        });

PipelineStageDefinition<BsonDocument, BsonDocument> forthStage
    = PipelineStageDefinitionBuilder.Sort(Builders<BsonDocument>.Sort.Ascending("warehouseId"));

var result = _collection.Aggregate()
    .AppendStage(firstStage)
    .AppendStage(secondStage)
    .AppendStage(thirdStage)
    .AppendStage(forthStage)
    .ToList();

Demo