How do I update a field in a sub-document array with a field from the document in MongoDB?

Question

I have a large amount of data (~160M items) where a date value wasn't populated on the sub-document array fields, but was populated on the parent document. I'm very new to MongoDB and having trouble figuring out how to $set the field to match. Here's a sample of the data:

{
   "_id": "5f11d4c48663f32e940696ed",
   "Widgets":[{
         "WidgetId":663,
         "Name":"Super Widget 2.0",
         "Created":null,
         "LastUpdated":null
      }],
   "Status":3,
   "LastUpdated":null,
   "Created": "2018-11-09T18:22:16.000Z"
   }
}

My knowledge of MongoDB is pretty limited but here's the basic aggregation I have created for part of the pipeline and where I'm struggling:

db.sample.aggregate(
    [
        { 
            "$match" : { 
                "Donors.$.Created" : { 
                    "$exists" : true
                }
            }
        }, 
        { 
            "$match" : { 
                "Widgets.$.Created" : null
            }
        }, 
        { 
            "$set" : { 
                "Widgets.$.Created" : "Created"     // <- This is where I can't figure out how to define the reference to the parent "Created" field
            }
        }
    ]
);

The desired output would be:

{
   "_id": "5f11d4c48663f32e940696ed",
   "Widgets":[{
         "WidgetId":663,
         "Name":"Super Widget 2.0",
         "Created":"2018-11-09T18:22:16.000Z",
         "LastUpdated":null
      }],
   "Status":3,
   "LastUpdated":null,
   "Created": "2018-11-09T18:22:16.000Z"
   }
}

Thanks for any assitance

if you want to update in collection then you can try this arrayFilters, like stackoverflow.com/a/63186367/8987128 you can use in updateMany() function also. — turivishal
– turivishal, Commented Aug 5, 2020 at 14:28
Interesting, what's the advantage of using that process over an aggregate? With 160million records to update I'm expecting a pretty significant performance hit (230,000 documents, average of 1,000 array items per document) — Kevin O.
– Kevin O., Commented Aug 5, 2020 at 14:47
Thank you, that's helpful! Still I'm extremely new to Mongo and there's no one one my team with any extensive knowledge (won't get into details but it is what it is), and I'm not seeing in the documentation how I would define the value for the Widgets.$.Created field to equal the Created field in the document. If you have time, could you post a possible solution? Much appreciated! — Kevin O.
– Kevin O., Commented Aug 5, 2020 at 15:08

GitGitBoom · Accepted Answer · 2020-08-05 15:34:28Z

2

Are you attempting to add the Created field to sub documents on query/aggregation? Or are you attempting to update/save the Created field on the subdocuments?

The $ is an update operator, to be used with updateMany or updateOne. Not aggregate. https://docs.mongodb.com/manual/reference/operator/query-array/

https://docs.mongodb.com/manual/reference/operator/update-array/

If you just want to add the parents Created field to all subdocuments on query/aggregation this is all you have to do: https://mongoplayground.net/p/yHDHULCSTIz

db.collection.aggregate([
  {
    "$addFields": {
      "Widgets.Created": "$Created"
    }
  }
])

If your attempting to save the parents Created field to all subdocuments:

db.sample.updateMany({"Widgets.Created" : null}, [{$set: {"Widgets.Created" : "$Created"}}])

Note: This matches any doc that has a subdocument with a null Created field and updates all the subdocuments.

answered Aug 5, 2020 at 15:34

GitGitBoom

1,9226 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kevin O. Over a year ago

I'm attempting to save the parents' Created field to the sub-document associated, but when I attempted to use the query provided I got the following error: Error: the update operation document must contain atomic operators. The sub-documents all have a Created field that is null after a data migration from MariaDB to MongoDB. The parent Document's Created field is a valid date that I can use to populate the sub-document field

GitGitBoom Over a year ago

Did you include the $set part? The array wrapping the update is is important too

GitGitBoom Over a year ago

Yes, I've tested in 4.2.2 and 4.0 in shell, both worked. Thanks @turivishal

Kevin O. Over a year ago

I am using Studio 3T's intellishell, will try in shell directly

Kevin O. Over a year ago

Found the issue, and it now works in both shell and Studio 3T:

db.getCollection("sandbox.sample").update({"Widgets.Created": null}, [{$set: {"Widgets.Created" : "$Created"}}], {multi: true});

The {multi: true} was missed which prevented it from working in S3T, and successfully updated each document in the sample set.

|

Collectives™ on Stack Overflow

How do I update a field in a sub-document array with a field from the document in MongoDB?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related