merge two fields into an object

Question

I am trying to merge two fields here is my collection

db.Acc_Details.findOne()
{ 
    "_id": ObjectId("577f43fe748646cc91370713"), 
    "Acc_Id": 1, 
    "Name": "xxxxx", 
    "Phone": NumberLong("123456789"), 
    "Email": "xxxxxx@gmail.com" 
}

Now, I want to combine Phone and Email into contact and update this collection

db.Acc_Details.findOne()
{ 
    "_id": ObjectId("577f43fe748646cc91370713"), 
    "Acc_Id": 1, 
    "Name": "xxxxx", 
    "Contact": {
        "Phone": NumberLong("123456789"), 
        "Email": "xxxxxx@gmail.com"
    } 
}

This is what I have tried but I don't know if it's right or not:

db.Acc_Details.aggregate([
    {
        $project: {
            "Contact": {
                "$map": {
                    input: { $literal: ["p1", "p2"] },
                    as: "p",
                    in: {
                        $cond: [
                           { $eq: ["$$p", "p1"] },
                           "$Phone",
                           "$Email"
                        ]
                    }
                }
            }
        }
    },
    { $unwind: "$Contact" }
])

Result is

{ "_id" : ObjectId("577f43fe748646cc91370713"), "Contact" : NumberLong("12356789") }
{ "_id" : ObjectId("577f43fe748646cc91370713"), "Contact" : "xxxxxx@gmail.com" }

Can someone help me with this?

chridam · Accepted Answer · 2016-09-28T15:55:31.357

That's a bit overkill. A much simpler pipeline follows:

db.Acc_Details.aggregate([
    {
        "$project": {
            "Acc_Id": 1, 
            "Name": 1, 
            "Contact": {
                "Phone": "$Phone", 
                "Email": "$Email"
            } 
        }
    }
])

To update the table with the new schema, you need to leverage your updates with the bulkWrite() API which is more efficient for the task. Consider the following bulk update operation where you just iterate using the find() cursor and update the fields as:

var ops = [];
db.Acc_Details.find().snapshot().forEach(function(doc) {
    ops.push({
        "updateOne": {
            "filter": { "_id": doc._id },
            "update": {
                "$set": { 
                    "Contact": { "Phone": doc.Phone, "Email": doc.Email }
                },
                "$unset": { "Phone": "", "Email": "" }
            }
        }
    });

    if (ops.length === 500) {
        db.Acc_Details.bulkWrite(ops);
        ops = [];
    }
})

if (ops.length > 0)  db.Acc_Details.bulkWrite(ops);

Or using the above aggregate results:

var ops = [];
db.Acc_Details.aggregate([
    {
        "$project": {
            "Contact": {
                "Phone": "$Phone", 
                "Email": "$Email"
            } 
        }
    }
]).forEach(function(doc) {
    ops.push({
        "updateOne": {
            "filter": { "_id": doc._id },
            "update": {
                "$set": { "Contact": doc.Contact },
                "$unset": { "Phone": "", "Email": "" }
            }
        }
    });

    if (ops.length === 500) {
        db.Acc_Details.bulkWrite(ops);
        ops = [];
    }
})

if (ops.length > 0)  db.Acc_Details.bulkWrite(ops);

Or for MongoDB 2.6.x and 3.0.x releases use this version of Bulk operations:

var bulk = db.Acc_Details.initializeUnorderedBulkOp(),
    counter = 0;
db.Acc_Details.find().snapshot().forEach(function(doc) {
    bulk.find({ "_id": doc._id }).updateOne({
        "$set": { 
            "Contact": { "Phone": doc.Phone, "Email": doc.Email }
        },
        "$unset": { "Phone": "", "Email": "" }
    });

    if (counter % 500 === 0) {
        bulk.execute();
        bulk = db.Acc_Details.initializeUnorderedBulkOp();
    }
});

if (counter % 1000 !== 0 )  bulk.execute();

The Bulk operations API in both cases will help reduce the IO load on the server by sending the requests only once in every 500 documents in the collection to process.

merge two fields into an object

1 Answers1