MongoDB query to update each field of documents in collection with formula

user_10925957 0
Viewed 114

I have 5 documents in MongoDB collection which stores the temperature values in Celsius. I need to create a query so that I can convert each value into Fahrenheit. The document is in below format:

{
    "_id" : ObjectId("5c3eecfb5c4d120a7c4f5deb"),
    "sensorType" : "temperature",
    "sensorData" : [
        {
            "time" : "2018-08-31T19:32:15Z",
            "value" : 16
        },
        {
            "time" : "2018-08-31T19:47:21Z",
            "value" : 18
        },
        {
            "time" : "2018-08-31T20:02:21Z",
            "value" : 23
        },
        {
            "time" : "2018-08-31T20:17:21Z",
            "value" : 19
        },

It has time and the value, I need to convert the value in complete sensorData.

To resolve this, I started with the below query:

db.sensordata.updateMany(
{ sensorType: "temperature" },
{
$set: { "sensorData.$[].value": 8*2 },
}
)

In the above query, I have selected sensorType as temperature because the collection contains other sensorType as well. In $set, I am just using a simple formula 8*2 just to check if the query works or not. By running the above query, all the values are updated to 16, but I am not able to first fetch the current value and then do the math to convert it into fahrenheit. Below is the formula which we can use:

celsius * 9/5  + 32

I tried with below query as well but it gave me error:

db.sensordata.updateMany(
{  sensorType: "temperature"}, 
{
 $set:{"sensorData.$[].value": "($sensorData.value * 9/5) + 32"}
 })

But instead of converting it is saving the value as ($sensorData.value * 9/5) + 32. Can anyone please help me in creating this query?

mongodb
2 Answers

I found a solution but it's in two steps:

Step 1: run query to multiply by 9/5

    db.sensordata.update(
     { 
        sensorType: "temperature"
     }, 
     {
        $mul: { "sensorData.$[].value": 9/5 }
     },
     { multi:true}
    )

Step 2: Increase 32 (adding) in results as below:

    db.sensordata.update(
     { 
        sensorType: "temperature"
     }, 
     {
        $inc: { "sensorData.$[].value": 32 }
     },
     { multi:true }
    )
user_49987310

You can't multiple inside $set. But instead of converting it is saving the value as ($sensorData.value * 9/5) + 32 -> This is right because you're assigning that value not multiplying the value

You can do this thing but for this, you need two queries

First, multiply the document, the below query multiplied the 9/5 value to the existing value

db.sensor.updateMany(
{  sensorType: "temperature"},
  { $mul: {"sensorData.$[].value": 9/5}}
)

Then increment the document, the below query adds the 32 value to the existing value

db.sensor.updateMany(
{  sensorType: "temperature"},
  { $inc: {"sensorData.$[].value": 32}} 
)

Happy Mongodb Querying

user_62621690
Related