lookup with sum in mongodb with python

Question

I have two collections, one with a lot of transactions and another one with a selected list of companies, that belong to an index and may or may not have made some transactions. In the first list there are many companies that do not belong to the second collection. I want to know the total transaction amount the companies in the second collection have done. The field that is the same in both collections is called ISIN.

Transactions

{"_id": "1", "Name": "Company 1", "ISIN": "123456789111", "transactionAmount": "14000"}
{"_id": "2", "Name": "Company 1", "ISIN": "123456789111", "transactionAmount": "180000"}
{"_id": "3", "Name": "Company 2", "ISIN": "123456789115", "transactionAmount": "23400"}
{"_id": "4", "Name": "Company 3", "ISIN": "123456789117", "transactionAmount": "1000"}

Companies

{"_id": "c1", "Name_short": "Comp 1", "ISIN": "123456789111"}
{"_id": "c2", "Name_short": "Comp 2", "ISIN": "123456789115"}
{"_id": "c3", "Name_short": "Comp 3", "ISIN": "123456789117"}
{"_id": "c4", "Name_short": "Comp 4", "ISIN": "123456789118"}

Expected result

{"Name": "Company 1", "ISIN": "123456789111", "transactionAmount": "194000"}
{"Name": "Company 2", "ISIN": "123456789115", "transactionAmount": "23400"}
{"Name": "Company 3", "ISIN": "123456789117", "transactionAmount": "1000"}
{"Name": "Company 4", "ISIN": "123456789118", "transactionAmount": "0"}

I tried it with lookup, but have not been succesfull so far. But it is the first time I am using lookup, so probably didn't find the right code so far.

Anybody got an idea?

Tom Slabbaert · Accepted Answer · 2022-10-30 12:56:58Z

1

As you mentioned, a standard $lookup stage is the way to go:

db.companies.aggregate([
  {
    $lookup: {
      from: "trannsactions",
      localField: "ISIN",
      foreignField: "ISIN",
      as: "matchedTransactions"
    }
  },
  {
    $project: {
      _id: 1,
      name: "$Name_short",
      ISIN: "$ISIN",
      transactionAmount: {
        $sum: {
          $map: {
            input: "$matchedTransactions.transactionAmount",
            in: {
              $toDouble: "$$this"
            }
          }
        }
      }
    }
  }
])

Mongo Playground

answered Oct 30, 2022 at 12:56

Tom Slabbaert

22.5k10 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

lookup with sum in mongodb with python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related