Mongodb updateMany property to exact value of other property

Question

I have documents representing Users with onboarding data stored as a nested object:

{
    "_id" : ObjectId("5c7eb0132e6f793bcc7f4bf7"),
    "userName" : "sample_user_name",
    "onBoarding" : {
        "completed" : ISODate("2019-03-05T17:46:28.803Z"),
        "stepId" : 8,
        "started" : null
    }
}

But due to a bug we are missing a date when onboarding was started, I would like to "retrieve" this information by running an update operation where "started" will be set to the same date as "completed". I have a query like:

db.getCollection('user').updateMany(
{ 
  $and: [
      {"onBoarding.started": {$exists: false}},
      {"onBoarding.completed": {$exists: true}}
]}, 
{
  $set: { "onBoarding.started": "$onBoarding.completed" }
})

This however, sets "started" to "$onBoarding" literally (as a string).

{
    "_id" : ObjectId("5c7eb0132e6f793bcc7f4bf7"),
    "userName" : "sample_user_name",
    "onBoarding" : {
        "completed" : ISODate("2019-03-05T17:46:28.803Z"),
        "stepId" : 8,
        "started" : "$onBoarding"
    }
}

How should I write it for mongo to take a value from "onBoarding.completed" and copy this value to "onBoarding.started"?? Expected result document should look like:

{
    "_id" : ObjectId("5c7eb0132e6f793bcc7f4bf7"),
    "userName" : "sample_user_name",
    "onBoarding" : {
        "completed" : ISODate("2019-03-05T17:46:28.803Z"),
        "stepId" : 8,
        "started" : ISODate("2019-03-05T17:46:28.803Z")
    }
}

I think this might answers to your question: https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field Depends on which version of MongoDB are you using. — Nicolae Maties, Dec 09 '19 at 17:22
It doesn't, it uses a $concat function which I don't want to use here, how do you even $concat two dates? I want to set "started" to exactly same value as "completed". — enterbios, Dec 09 '19 at 17:24
$concat there is just an example to produce a new value from 2 fields — Alex Blex, Dec 09 '19 at 17:48
Does this answer your question? [Update MongoDB field using value of another field](https://stackoverflow.com/questions/3974985/update-mongodb-field-using-value-of-another-field) — Alex Blex, Dec 09 '19 at 17:52
No @AlexBlex it doesn't answer my question. I'm doing updateMany in the same way but without using a $concat function it doesn't work, instead of a date from "onBoarding.completed" it puts "$onBoarding" literally as a string. — enterbios, Dec 09 '19 at 18:26
Please read and comprehend the answer - why it is working there and why there are different syntax for different versions. Follow the links to the docs and read details. Concat there is not essential. — Alex Blex, Dec 09 '19 at 21:27

score 3 · Answer 1 · answered Dec 09 '19 at 18:45

3

You need to use an aggregation pipeline to be able to use the value of another field :

db.user.updateMany(
   { <your query selector > },
   [
      { $set: { onBoarding.started: "$onBoarding.completed" } },
   ]
)

Be aware that here, $set refers to the aggregation pipeline stage and not the update operator $set : https://docs.mongodb.com/manual/reference/method/db.collection.updateMany/index.html#update-with-aggregation-pipeline

answered Dec 09 '19 at 18:45

Abk

376
3
10

How is that different from the operation I was running? – enterbios Dec 09 '19 at 19:14
2

The square brackets around the $set makes it an aggregation pipeline and not a "simple" $set operator. So it allows you to use the value of another field. – Abk Dec 09 '19 at 19:27

score 1 · Accepted Answer · answered Dec 10 '19 at 03:31

The exists operator checks for existence of a field. If a field has a value of null, the query still returns true (because the field still exists, only its value is null).

The following queries behave differently with this input document: { _id: 1, fld1: 123, fld2: null }

db.test.find( { fld2: { exists: false } } ) returns false.
db.test.find( { fld2: null } } ) returns true.

Coming back to the data in question - the following query / script will update all the documents with following condition: ( "onBoarding.started" is null or the field doesn't exist ) and ( "onBoarding.completed" field exists and is not null ).

db.test.find( { $and: [ { $or: [ { "onBoarding.started": null },  { "onBoarding.started": { $exists: false } } ] },  { $and: [ { "onBoarding.completed": { $exists: true } }, { "onBoarding.completed": { $ne: null } } ] } ] } ).forEach( doc => db.test.updateOne( { _id: doc._id }, { $set: { "onBoarding.started" : doc.onBoarding.completed } } ) )

Using MongoDB version 4.2, the same update can be run as follows. Note the update uses an Aggregation stage (see documetation on update).

db.test.updateMany(
  { $and: [ { $or: [ { "onBoarding.started": null },  { "onBoarding.started": { $exists: false } } ] },  { $and: [ { "onBoarding.completed": { $exists: true } }, { "onBoarding.completed": { $ne: null } } ] } ] },
  [
    { $addFields: 
        { "onBoarding.started" : "$onBoarding.completed" } 
    }
  ]
)

score 0 · Answer 3 · answered Mar 13 '23 at 05:03

0

Spring MongoDB version.

@Query("{<your query selector>}")
@Update(pipeline = {
    "{ $set : {onBoarding.started: "$onBoarding.completed" } }"
})
void update();

answered Mar 13 '23 at 05:03

logbasex

1,688
1
16
22

Mongodb updateMany property to exact value of other property

3 Answers3