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

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" }
...

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();
        }


    }
}