lookup with sum in mongodb with python-CodePudding

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?

CodePudding user response：

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