I want to count and sum all posts in Items. When i query my collection with: GetCollection().Find(p => p.Type == "Test") i receive this:

[
     {
          "Type": "Test",
          "Items": [
              { "Name": "123", "Id":"123" },
              { "Name": "123", "Id":"123" }
          ]
     },
     {
          "Type": "Test",
          "Items": [
              { "Name": "123", "Id":"123" },
              { "Name": "123", "Id":"123" }
          ]
     }
]

But in this case i want to count all posts in items and the result i want to get is: 4. How can i write a query using MongoDB C# driver to get this?

CodePudding user response：

This is how you can do in mongoDB via aggregation:

db.collection.aggregate([
{
  $match: {
    Type: "Test"
  }
},
{
 "$addFields": {
   "Items": {
     $size: "$Items"
  }
 }
},
{
 $group: {
  _id: "Sum",
    Total: {
    $sum: "$Items"
    }
  }
 }
])

Explained:

1. Match all documents where Type:"Test"
2. AddFields/$size to count the Items array elements per document.
3. Group/Sum to count total Items elements.

With small modification you can adapt to C#

Playground

CodePudding user response：

Assume that the result returns the array/list of Entity, you can work with System.Linq by flattening the list and getting the count of Items.

using System.Linq;

List<Entity> records = GetCollection()
    .Find(p => p.Type == "Test")
    .ToList();

int count = records
    .SelectMany(x => x.Items)
    .Count();

public class Entity
{
    public string Type { get; set; }
    public List<Item> Items { get; set; }
}

public class Item
{
    public string Name { get; set; }
    public string Id { get; set; }
}