Add a new field to a collection with value of an existing field

Question

I'd like to add a new field to a collection, with the value of the new field set to the value of an existing field.

Specifically, I'd like to go from this:

# db.foo.findOne()
    {
        "_id"     : ObjectId("4f25c828eb60261eab000000"),
        "created" : ISODate("2012-01-29T16:28:56.232Z"),
        "..."     : ...
    }

to this:

# db.foo.findOne()
    {
        "_id"      : ObjectId("4f25c828eb60261eab000000"),
        "created"  : ISODate("2012-01-29T16:28:56.232Z"),  
        "event_ts" : ISODate("2012-01-29T16:28:56.232Z"),  #same as created
        "..."      : ...
    }

(New documents in this collection won't all have this peculiar redundancy, but I want to do this for my existing documents)

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) — Aslam Shaik, Jun 12 '22 at 15:35

score 50 · Accepted Answer · answered Feb 10 '12 at 08:47

50

function addEventTsField(){
    db.foo.find().forEach(function(doc){
         db.foo.update({_id:doc._id}, {$set:{"event_ts":doc.created}});
    });
}

Run from console:

addEventTsField();

answered Feb 10 '12 at 08:47

driangle

11,601
5
47
54

2

Not an atomic operation though. These are two separate operations with the possibility of the data changing between in between. (although he's probably safe in his case as it seems he's doing it once, offline) – UpTheCreek Feb 18 '13 at 20:23

score 4 · Answer 2 · answered Apr 01 '20 at 19:55

Starting Mongo 4.2, db.collection.update() can accept an aggregation pipeline, finally allowing the creation of a field based on another field:

// {
//   "created" : ISODate("2012-01-29T16:28:56.232Z"),
//   "..." : "..."
// }
db.collection.updateMany({}, [{ $set: { event_ts: "$created" } }])
// {
//   "created" :  ISODate("2012-01-29T16:28:56.232Z"),
//   "event_ts" : ISODate("2012-01-29T16:28:56.232Z"),
//   "..." : "..."
// }

The first part {} is the match query, filtering which documents to update (here we keep all documents).
The second part [{ $set: { event_ts: "$created" } }] is the update aggregation pipeline (note the squared brackets signifying the use of an aggregation pipeline). $set is a new aggregation operator and an alias for $addFields, which allows including a new field to the document.

score 2 · Answer 3 · answered Feb 10 '12 at 08:35

2

No, it is not possible. Only two steps thats you've probably know:

Load document, read field. (you can load only specific fields: _id, and created in your case if performance is an issue)
Atomic update of document (set event_ts by using loaded created field)

answered Feb 10 '12 at 08:35

Andrew Orsich

52,935
16
139
134

Not even with something with db.foo.find().forEach() ? – Purrell Feb 10 '12 at 08:40
1

@Purrell: `forEach` actually load all found documents, and it become two steps update... – Andrew Orsich Feb 10 '12 at 08:44

Param · Answer 4 · 2015-01-14T10:59:59.370

If it is a one time thing to do, its no big deal. Run a query like "db.foo.find();" in your mongo shel and paste the entire output to a editor like Textpad or Sublime text, do a column block ( In sublime text it is Ctrl + scroll wheel click & Drag ) , , paste it which will extend to another field, rename the newly pasted column as you want it, once done, do a regex to just edit the whole bunch as "^" with "db.foo.insert({" and at the "$" as "});"

It is like the following steps.

You pasted the output like { 'field1' : value, 'field2' : value2 , 'field3' : value4 };

copy the last set and extend using the editor column block { 'field1' : value, 'field2' : value2 , 'field3' : value4 };

Now insert the appropriate words to make it look like a command for mongo db.foo.insert({ 'field1' : value, 'field2' : value2 , 'field3' : value4 });

Now copy them all and paste it in your mongo shell which will just do the whole thing for you as you want it. Test it once and redo the whole thing just after you cleanup the collection otherwise it will insert duplicates. Also remove the "_id" fields in the output since it will conflict when you attempt to insert again !!

Now, every single line in your editor shows a valid mongo command that can be pasted to your shell to insert a new document to your collection. so test it once and it works fine, clean up your entire collection like "db.foo.remove({});" and paste the entire command from the editor which will do it in a giffy.

It may be difficult when you attempt for the first time, but if you get it right its gonna be a handy thing for you to work along whenever you want it.... !!

This answer doesn't even make sense, as it's so error-prone, can't be automated, can't be tested, can't be scripted... Why would you ever want to do this operation in an editor, when it can be done so easily with a simple function and forEach() ? — David Makogon, Nov 16 '15 at 06:00
Also -1 because it requires `db.foo.remove({})` in the middle of the operation, which would screw up anything expecting the existing data to still be there. — David, Jan 26 '16 at 09:05
Question was posted with the intention it is a onetime work. Also, doing it in editor makes it much easier to what need to be edited, what to be commented or what to be removed. Of course the solution using foreach is better, this is not that bad too since it comes very handy when we are in the middle of testing wherein we need to insert data quite frequently, remove them, add them back again with few changes here n there etc., It can be automated too when u just run the same script file in command-line. — Param, Jan 26 '16 at 10:33

score -2 · Answer 5 · answered Feb 10 '12 at 12:07

-2

since your code already knows how to handle event_ts why do you need to copy the data? you can rename the field instead:

{ $rename : { old_field_name : new_field_name } }

See http://www.mongodb.org/display/DOCS/Updating

answered Feb 10 '12 at 12:07

Vitaly Kushner

9,247
8
33
41

6

You're making an assumption, that 'created' is not also needed in the documents. – Purrell Jun 24 '12 at 07:18

Add a new field to a collection with value of an existing field

5 Answers5