mongodb - $sum 返回 0

Question

此查询的目的是当您“给定一名员工返回总销售额”时。

我的第一个查询是这个

db.Employee.aggregate([{$lookup: {from: "Invoice", localField: "_id", foreignField: "_id", as: "Invoices"}}, {$match: {_id: 2}}]).pretty()

它返回了下面的代码片段，尽管它只返回了一个客户，即使员工有多个客户。我不完全确定为什么它只返回一个。

{
"_id" : 2,
"LastName" : "Edwards",
"FirstName" : "Nancy",
"Title" : "Sales Manager",
"ReportsTo" : 1,
"BirthDate" : ISODate("1958-12-08T00:00:00Z"),
"HireDate" : ISODate("2002-05-01T00:00:00Z"),
"Address" : "825 8 Ave SW",
"City" : "Calgary",
"State" : "AB",
"Country" : "Canada",
"PostalCode" : "T2P 2T3",
"Phone" : "+1 (403) 262-3443",
"Fax" : "+1 (403) 262-3322",
"Email" : "nancy@chinookcorp.com",
"Invoices" : [
    {
        "_id" : 2,
        "CustomerId" : 4,
        "InvoiceDate" : ISODate("2009-01-02T00:00:00Z"),
        "BillingAddress" : "Ullevålsveien 14",
        "BillingCity" : "Oslo",
        "BillingState" : null,
        "BillingCountry" : "Norway",
        "BillingPostalCode" : "0171",
        "Total" : 3.96,
        "InvoiceLines" : [
            {
                "_id" : 3,
                "TrackId" : 6,
                "UnitPrice" : 0.99,
                "Quantity" : 1
            },
            {
                "_id" : 4,
                "TrackId" : 8,
                "UnitPrice" : 0.99,
                "Quantity" : 1
            },
            {
                "_id" : 5,
                "TrackId" : 10,
                "UnitPrice" : 0.99,
                "Quantity" : 1
            },
            {
                "_id" : 6,
                "TrackId" : 12,
                "UnitPrice" : 0.99,
                "Quantity" : 1
            }
        ]
    }
]
}

为了解决这个问题并实现返回总销售额的目标，我创建了这个新查询

db.Employee.aggregate([{$unwind: "$_id"}, {$lookup: {from: "Invoice", localField: "_id", foreignField: "_id", as: "Invoices"}}, {$match: {_id: 2}}, {$group: {_id: "$_id", Total: {$sum: "$Total"}}}]).pretty()

尽管它只返回 { "_id": 2, "Total": 0 }

查看其他问题后，我认为这可能是因为文档是嵌套的，尽管尝试了可能的解决方案但没有产生任何输出。没有错误很好，但没有任何反应。这是我尝试过的查询:

db.Employee.aggregate([{$unwind: "$_id"}, {$unwind: "$_id.Invoices"}, {$unwind: "$_id.Invoices.InvoiceLines"}, {$lookup: {from: "Invoice", localField: "_id", foreignField: "_id", as: "Invoices"}}, {$match: {_id: 2}}, {$group: {_id: "$_id", Total: {$sum: "$Total"}}}]).pretty()

我不明白为什么这个查询没有返回总数。我尝试过的一切都失败了。感谢您的帮助。

编辑:

我的数据库结构如下:员工>客户>发票。客户通过 SupportRepId 引用员工，这与员工 ID 相同，因为每个客户都被分配了一名员工，发票包含客户 ID，因为每张发票都有一个客户。所以我想获取所有发票并根据员工 ID 计算总数。

员工示例:

{
    "_id":3,
    "LastName":"Peacock",
    "FirstName":"Jane",
    "Title":"Sales Support Agent",
    "ReportsTo":2,
    "BirthDate":    ISODate("1973-08-29T00:00:00    Z"),
    "HireDate":    ISODate("2002-04-01T00:00:00    Z"),
    "Address":"1111 6 Ave SW",
    "City":"Calgary",
    "State":"AB",
    "Country":"Canada",
    "PostalCode":"T2P 5M5",
    "Phone":"+1 (403) 262-3443",
    "Fax":"+1 (403) 262-6712",
    "Email":"jane@chinookcorp.com"
}

客户示例:

{
    "_id":1,
    "FirstName":"Luís",
    "LastName":"Gonçalves",
    "Company":"Embraer - Empresa Brasileira de Aeronáutica S.A.",
    "Address":"Av. Brigadeiro Faria Lima, 2170",
    "City":"São José dos Campos",
    "State":"SP",
    "Country":"Brazil",
    "PostalCode":"12227-000",
    "Phone":"+55 (12) 3923-5555",
    "Fax":"+55 (12) 3923-5566",
    "Email":"luisg@embraer.com.br",
    "SupportRepId":3
}

发票示例:

{
    "_id":2,
    "CustomerId":4,
    "InvoiceDate":    ISODate("2009-01-02T00:00:00    Z"),
    "BillingAddress":"Ullevålsveien 14",
    "BillingCity":"Oslo",
    "BillingState":null,
    "BillingCountry":"Norway",
    "BillingPostalCode":"0171",
    "Total":3.96,
    "InvoiceLines":[
        {
            "_id":3,
            "TrackId":6,
            "UnitPrice":0.99,
            "Quantity":1
        },
        {
            "_id":4,
            "TrackId":8,
            "UnitPrice":0.99,
            "Quantity":1
        },
        {
            "_id":5,
            "TrackId":10,
            "UnitPrice":0.99,
            "Quantity":1
        },
        {
            "_id":6,
            "TrackId":12,
            "UnitPrice":0.99,
            "Quantity":1
        }
    ]
}

Answer 1

看看这个能不能用

db.Employee.aggregate([
  {
    $match: {
      "Employee._id": 2
    }
  },
  {
    $lookup: {
      from: "Customer",
      localField: "_id",
      foreignField: "customer.SupportRepId",
      as: "customer"
    }
  },
  {
    $lookup: {
      from: "Invoice",
      localField: "customer._id",
      foreignField: "invoice.CustomerId",
      as: "invoice"
    }
  }
])