CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-frameworkmongoose-schema
JAN
JAN

Reputation: 21875

MongoDB : How to multiply a field that appears only in $project?

I'm joining ($lookup) two collection the following way : 

  const LEAD_PRICE  = ....; // doesn't matter 
  Client.aggregate(
      [
        {
          $lookup: {
            from: "clientboughtleads",
            localField: "_id",
            foreignField: "Client",
            as: "ClientLeads"
          }
        },

        {
          $project: {
            ClientId: "$_id",
            RegistrationDate: "$date",
            TotalPurchasedLeads: {
              $size: "$ClientLeads"
            },
            IncomeFromClient: {     // This one is always undefined
              $multiply: [LEAD_PRICE / 2, "$TotalPurchasedLeads"]
            }
          }
        }
      ]

I want to calculate IncomeFromClient by the mul of a const and a field that is been also calculated - TotalPurchasedLeads , however it returns NULL : 

 [
[0]   {
[0]     _id: 5e0e43ae82a71e0017dccb20,
[0]     ClientId: 5e0e43ae82a71e0017dccb20,
[0]     RegistrationDate: 2020-01-02T21:25:34.000Z,
[0]     TotalPurchasedLeads: 4,
[0]     IncomeFromClient: null      // This one is not calculated 
[0]   }
[0] ]

How can I get the size of the $lookup collection when calculating a field that is being $projected ?

Upvotes: 2

Views: 351

Answers (2)

Ashh
Ashh

Reputation: 46481

You cannot use the $projected value in the same $projection stage. So instead use its value

{
  "$project": {
    "ClientId": "$_id",
    "RegistrationDate": "$date",
    "TotalPurchasedLeads": {
      "$size": "$ClientLeads"
    },
    "IncomeFromClient": {
      "$multiply": [LEAD_PRICE / 2, { "$size": "$ClientLeads" }]
    }
  }
}

Upvotes: 1

Mahesh Bhatnagar
Mahesh Bhatnagar

Reputation: 1080

Please use addFields keyword after we can use key in project .

 const LEAD_PRICE  = ....; // doesn't matter 
  Client.aggregate(
      [
        {
          $lookup: {
            from: "clientboughtleads",
            localField: "_id",
            foreignField: "Client",
            as: "ClientLeads"
          }
        },
   {
     $addFields: {
       TotalPurchasedLeads: {
              $size: "$ClientLeads"
          }
     }
   },
        {
          $project: {
            ClientId: "$_id",
            RegistrationDate: "$date",
            TotalPurchasedLeads:1,
            IncomeFromClient: {     // This one is always undefined
              $multiply: [LEAD_PRICE / 2, "$TotalPurchasedLeads"]
            }
          }
        }
      ]

Upvotes: 1

Related Questions