Question 174145 in Mongodb Question posted in
The official documentation can be found here.

MongoDb. Update any field if the value is negative

I have a collection with documents like this:

{
  "_id": "5f7db1c8e3ec502bf2f2c97c",
  "subject": "AA0001",
  "metrics": {
     "m001": 40.8,
     "m0022": 58.8,
     .....
     "m0101: -5.0,
  }
}

All the fields inside "metrics" are numbers. The negative values are wrong. I want to replace any negative value with "NA". But I have two problems:

  • The fields with negative values are different for each document.
  • The fields inside "metrics" are not always the same. One document can have more "metrics" than other.

¿Any idea?

Tags:

2

Answers


  1. 0

    you can use $objectToArray to convert metrics object into an array of key-value pair objects and then $map it to transform the array in a way if the number is negative it will be replaced with "NA" using a condition operation. After doing the transformation you can convert the array back to an object using $arrayToObject

    db.collection.aggregate([
  {
    $addFields: {
      metrics: {
        $map: {
          input: { $objectToArray: "$metrics" },
          in: {
            k: "$$this.k",
            v: { $cond: [ { $gte: [ "$$this.v", 0 ] }, "$$this.v", "NA" ] }
          }
        }
      }
    }
  },
  {
    $addFields: {
      metrics: { $arrayToObject: "$metrics" }
    }
  }
])

    playground

    you can also update the collection to reflect these changes. Since Mongo 4.2 you can write aggregation pipeline inside update calls

    $addFields and $set can be used interchangeably

    db.collection.update({},
[
  {
    $addFields: {
      metrics: {
        $map: {
          input: { $objectToArray: "$metrics" },
          in: {
            k: "$$this.k",
            v: { $cond: [ { $gte: [ "$$this.v", 0 ] }, "$$this.v", "NA" ] }
          }
        }
      }
    }
  },
  {
    $addFields: {
      metrics: { $arrayToObject: "$metrics" }
    }
  }
],
{
  multi: true
})

    playground

    Login or Signup to reply.
  2. 0

    You can use below code.

    db.collection.updateMany(
  { "metrics": { $exists: true } },
  [
    {
      $set: {
        "metrics": {
          $arrayToObject: {
            $map: {
              input: { $objectToArray: "$metrics" },
              in: {
                k: "$$this.k",
                v: {
                  $cond: {
                    if: { $lt: [ "$$this.v", 0 ] },
                    then: "NA",
                    else: "$$this.v"
                  }
                }
              }
            }
          }
        }
      }
    }
  ]
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search