mongodb - 使用展开创建单独的文档后如何计算值的总和？

Question

我正在努力在 MongoDB 中开发一个管道，它将公司每个州的所有 number_of_employees 加起来，并打印出每个州的信息。

集合结构的基础知识如下所示：

"name" : "AdventNet",
"number_of_employees" : 600,
"offices" : [
       {
           "description" : "Headquarters",
           "address1" : "4900 Hopyard Rd.",
           "address2" : "Suite 310",
           "zip_code" : "94588",
           "city" : "Pleasanton",
           "state_code" : "CA",
           "country_code" : "USA",
           "latitude" : 37.692934,
           "longitude" : -121.904945
        },
        {
            "description" : "",
            "address1" : "270 Lafayette Street",
            "address2" : "Suite 505",
            "zip_code" : "10012",
            "city" : "New York",
            "state_code" : "NY",
            "country_code" : "USA",
            "latitude" : 40.7237306,
            "longitude" : -73.9964312
        }
],

我尝试了几种不同的方法来获取我最新的管道代码，但我仍然在努力让总和正常工作。我尝试了不同的分组结构，但我一直收到错误消息。

db.research.aggregate( [
    {$match : {"offices.country_code" : "USA"} }, 
    {$project: {State : "$offices.state_code"}}, 
    {$unwind: "$State"}, 
    { $group : {"_id" : "$State", "total_employees": {$sum : "$number_of_
employees"} }}, 
    {$project: {_id : 0, State: "$_id", total_employees: 1}}
])

预期结果：

{ "total_employees" : 1500, "State" : "SD" }
{ "total_employees" : 350, "State" : "WV" }
...

实际结果：

{ "total_employees" : 0, "State" : "SD" }
{ "total_employees" : 0, "State" : "WV" }
...

标签： mongodbsumpipeline

Answer 1

我相信这是您正在寻找的管道：

db.research.aggregate([
    {
        "$match": {
            "offices.country_code": "USA"
        }
    },
    {
        "$unwind": "$offices"
    },
    {
        "$project": {
            "number_of_employees": "$number_of_employees",
            "state_code": "$offices.state_code",
            "_id": 0
        }
    },
    {
        "$group": {
            "_id": "$state_code",
            "total_employees": {
                "$sum": "$number_of_employees"
            }
        }
    },
    {
        "$project": {
            "Total_Employees": "$total_employees",
            "State": "$_id",
            "_id": 0
        }
    }
])

你可以看到它在这里工作：https ://mongoplayground.net/p/XUA_e9Y4Fq_

如果有人感兴趣，这里是生成上述查询的 c# 代码：

using MongoDB.Entities;
using System.Linq;

namespace StackOverflow
{
    public class Program
    {
        public class company : Entity
        {
            public int number_of_employees { get; set; }
            public office[] offices { get; set; }
        }


        public class office
        {
            public string state_code { get; set; }
            public string country_code { get; set; }
        }

        private static void Main(string[] args)
        {
            new DB("test");

            (new[]
            {
                new company{
                    number_of_employees = 100,
                    offices = new[]
                    {
                        new office
                        {
                            state_code = "NY",
                            country_code = "USA"
                        }
                    }
                },
                new company{
                    number_of_employees = 100,
                    offices = new[]
                    {
                        new office
                        {
                            state_code = "NY",
                            country_code = "USA"
                        },
                        new office
                        {
                            state_code = "LA",
                            country_code = "USA"
                        }
                    }
                }
            }).Save();

            var result = DB.Queryable<company>()
                           .Where(c => c.offices.Any(o => o.country_code == "USA"))
                           .SelectMany(c => c.offices, (c, o) => new { c.number_of_employees, o.state_code })
                           .GroupBy(c => c.state_code)
                           .Select(g => new
                           {
                               Total_Employees = g.Sum(c => c.number_of_employees),
                               State = g.Key
                           })
                           .ToList();
        }


    }
}