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.

CodePudding user response：

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
    }
  }
])