Mongodb findoneandupdate with aggregation pipeline

user_13663482 0
Viewed 1351

I have a data in MongoDB like this

    {"_id":{"$oid":"5ec4ed0b94996124187b95ce"},
           "cid":"245345323",
     "dtl":[
          {"_id":{"$oid":"5ed5ab655df7930ec424be1e"},
              "sid":"test",
              "amt":{"$numberInt":"48"},
              "Name":"test"},
          {"_id":{"$oid":"5ed5ac9f79a75423d4bc0270"},
             "sid":"s",
             "amt":{"$numberInt":"14"},
             "Name":"sfds"},
          {"_id":{"$oid":"5ed5ace7d204ab144863ed7a"},
             "sid":"wesad",
             "amt":{"$numberInt":"6"},
             "Name":"2sdad"
          },
        "updated":{"$date":{"$numberLong":"1589964043787"}},
        "__v":{"$numberInt":"6"},
       "TotalAmt":{"$numberInt":"68"}}

I need to update the TotalAmt field only if amt field in the array has value greater than 10. I have used the below 2 options

PM.findOneAndUpdate(
      {
        _id: MongoId,
      },
      [
        {
          $set: {
            //TotalAmt: { $sum: '$dtl.amt' },
            /*TotalAmt: {
              $cond: {
                if: { $gte: ['$dtl.amt', 10] },
                then: { $sum: '$dtl.amt' },
                else: 0,
              },
            },*/
          },
        },
      ],
      { new: true }
    )

It works fine without amt > 10 condition (first option commented). But not working if I add the condition. Any help would be appreciated. Thanks

mongoose mongodb-query mongodb aggregate-functions
2 Answers

Try using $map to change the $sum's input structure:


PM.findOneAndUpdate(
    {
        _id: MongoId,
    },
    [
        {
            $set: {
                TotalAmt: {
                    $cond: {
                        if: {$gte: ['$dtl.amt', 10]},
                        then: {
                            $sum: {
                                $map: {
                                   input: '$dtl',
                                   as: "item",
                                   in: "$$item.amt" 
                                }
                            },
                        },     
                        else: 0, // 0 or "$TotalAmt" ? 
                    },
                },
            },
        },
    ],
    {new: true}
)
user_107703700

You need to use $filter operator:

PM.findOneAndUpdate(
  {
    _id: MongoId,
  },
  [
    {
      $set: {
        TotalAmt: { 
          $sum: {
            $filter: {
              input: '$dtl.amt',
              cond: { $gte: ['$$this', 10] }
            }
          }
        }
      }
    }
  ]
  { new: true }
)

---Output---

{
    "_id" : ObjectId("5ec4ed0b94996124187b95ce"),
    "cid" : "245345323",
    "dtl" : [ 
        {
            "_id" : ObjectId("5ed5ab655df7930ec424be1e"),
            "sid" : "test",
            "amt" : 48,
            "Name" : "test"
        }, 
        {
            "_id" : ObjectId("5ed5ac9f79a75423d4bc0270"),
            "sid" : "s",
            "amt" : 14,
            "Name" : "sfds"
        }, 
        {
            "_id" : ObjectId("5ed5ace7d204ab144863ed7a"),
            "sid" : "wesad",
            "amt" : 6,
            "Name" : "2sdad"
        }
    ],
    "updated" : ISODate("2020-05-20T08:40:43.787Z"),
    "__v" : 6,
    "TotalAmt" : 62
}

Edit: If you want filter by other field, we need to add extra $map operator.

PM.findOneAndUpdate(
  {
    _id: MongoId,
  },
  [
    {
      $set: {
        TotalAmt: { 
          $sum: {
            $map:{
                input: {
                  $filter: {
                    input: '$dtl',
                    cond: { $eq: ['$$this.Name', 'test'] }
                  }
                },
                in:"$$this.amt"
            }
          }
        }
      }
    }
  ],
  { new: true }
)
user_37104900
Related