mongodb - $sum returns 0

Question

The aim of this query is when you are "Given an employee return a total sales number".

My first query was this

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

which returned the below snippet though it only returns one customer even though the employee has several customers. I'm not totally sure why it returns just one.

{
"_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
            }
        ]
    }
]
}

In an attempt to get around this and achieve my aim of returning the total sales number I created this new query

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

though it just returns { "_id" : 2, "Total" : 0 }

Having looked at other issues I'm thinking it might be because the documents are nested though having tried the potential solution it has yielded no output. No errors which is good but nothing happens. Here is the query I tried:

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

I don't understand why this query is not returning the total. Everything I've tried has failed. Any help is appreciated.

Edit:

My database is structured as so: Employee>Customer>Invoice. Customer references Employee via SupportRepId which is the same as an employee id as each customer is assigned an employee and Invoice contains the customer id as each invoice has a customer. So I want to get all invoices and sum there total based on an employee id.

Employee Example:

{
    "_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"
}

Customer Example:

{
    "_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
}

Invoice Example:

{
    "_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
        }
    ]
}

it is because of `{$match: {_id: 2}}` It only matches the document with `_id` of 2 — RisingSun, Feb 11 '19 at 20:04
Hi, the match id is there to match the employee with id 2 to all customers served by the employee but my output only displays one as opposed to the 5 there should be. — BR1888, Feb 11 '19 at 20:19
Ah I see I'm matching id 2 in employee with the invoice that has id 2 not all invoices that have employee id 2. — BR1888, Feb 11 '19 at 20:32
correct. Providing more samples from the Invoice document will help clarify the problem as well. Are you saving any info on which salesman those Invoices belong to? Like you said, you are matching the invoice id with the salesman id — RisingSun, Feb 11 '19 at 20:35
I've added examples of Employee, Customer and Invoice data and a short explanation of how Employee is connected to Invoice through Customer. All after edit. — BR1888, Feb 11 '19 at 20:48
So I'm guessing the `CustomerId` field in Invoices references the `_id` field in customers. Does `SupportRepId` reference the `_id` field in employee? If so, you need to join on those fields rather than just matching `_id` to `_id` — RisingSun, Feb 11 '19 at 21:03
Thanks for the edit. It looks much better and yes the SupportRepId is a direct reference to the employee _id. So employee _id: 2 is the employee referenced when it says SupportRepId: 2. — BR1888, Feb 11 '19 at 21:07
In this case you will need multiple `$lookup` operations. https://stackoverflow.com/questions/5681851/mongodb-combine-data-from-multiple-collections-into-one-how Pretty simple operation. And when you need to do a match, you need to specify which `_id` you are referencing by using the collection name like `Employee._id` — RisingSun, Feb 11 '19 at 21:11

RisingSun · Answer 1 · 2019-02-11T21:35:40.980

0

See if this works

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

edited Feb 11 '19 at 21:35

answered Feb 11 '19 at 21:17

RisingSun

1,693
27
45

There is no output when I enter the query. – BR1888 Feb 11 '19 at 21:23
I might have entered the fields wrong, please check those and retry – RisingSun Feb 11 '19 at 21:30
this also might not be the answer you are looking for. you might need to do nested lookups https://stackoverflow.com/questions/52712240/how-to-do-nested-lookup-search-in-mongodb – RisingSun Feb 11 '19 at 21:38
Yeah it's still not working I have modified it abit and produced abit of a mess. It seems to return all customers but no invoices. I'll take a look at the nested look ups you suggested. – BR1888 Feb 11 '19 at 21:57
https://stackoverflow.com/questions/36019713/mongodb-nested-lookup-with-3-levels check out this question. seems very similar to your problem – RisingSun Feb 11 '19 at 22:33
So I managed to solve that issue thanks to you and have also solved the issue around $sum so thanks for your help :) – BR1888 Feb 12 '19 at 16:55
glad i was able to help – RisingSun Feb 12 '19 at 17:40

mongodb - $sum returns 0

1 Answers1