CODEWITHSUNDEEP
mongodbexpressmongooseaggregation-framework
Omar Zahir
Omar Zahir

Reputation: 215

Mongodb positional operator " $ " with $sum operation not working

I'm working on an e-commerce web app, I use MEAN stack. I have a collection for orders, each order has a payment field which has subdocuments for payment data for (affiliate marketer, sellersPayment, website, referral) my question is about the sellersPayment field, I have an array like

      sellersPayment: [
        {
          amount: 50,
          isPaid: false,
          seller: ObjectId('seller 1 id'),
        },
        {
          amount: 80,
          isPaid: true,
          seller: ObjectId('seller 2 id'),
        },
      ]

the thing is I want to query orders which has a specific seller on the field seller inside that array ant then sum the amount field this is my approach:

    await Order.aggregate([
      {
        $match: {
          "payment.sellersPayment": {
            $elemMatch: {
              seller: ObjectId(user._id),
              isPaid: false,
            },
          },
        }      },
      {
        $group: {
          _id: null,
          confirmedBalance: { $sum: "$payment.sellersPayment.$.amount" },
        },
      },
      {
        $project: {
          confirmedBalance: 1,
        },
      },
    ]);

I get this error

" FieldPath field names may not start with '$' " any solutions ?

Upvotes: 1

Views: 91

Answers (1)

turivishal
turivishal

Reputation: 36104

I get this error " FieldPath field names may not start with '$' " any solutions ?

confirmedBalance: { $sum: "$payment.sellersPayment.$.amount" },

This is the invalid syntax, you can not use $ sign to access array elements,

There are few fixes,

  • $match you condition is correct
  • $unwind deconstruct sellersPayment array
  • $match match again your first stage condition to filter subdocument of sellersPayment
  • $group by null and sum amount using field payment.sellersPayment.amount
  • $project to show required fields
await Order.aggregate([
  {
    $match: {
      "payment.sellersPayment": {
        $elemMatch: {
          seller: ObjectId(user._id),
          isPaid: false
        }
      }
    }
  },
  { $unwind: "$payment.sellersPayment" },
  {
    $match: {
      "payment.sellersPayment.seller": ObjectId(user._id),
      "payment.sellersPayment.isPaid": false
    }
  },
  {
    $group: {
      _id: null,
      confirmedBalance: { $sum: "$payment.sellersPayment.amount" }
    }
  },
  {
    $project: {
      _id: 0,
      confirmedBalance: 1
    }
  }
])

Playground

Upvotes: 1

Related Questions