Flatten a nested object in MongoDB and rename

问题:

Suppose I have documents like this

{
    "id" : "1415166669",
    "color" : {
        "14" : "Silver"
    },
    "name":"Random Name"
}

where the key inside color can change, i.e Next Document may look like this

{
    "id" : "1415126969",
    "color" : {
        "11" : "Gold"
    },
    "name":"Random Name 2"
}

I want to flatten and rename them so that my documents have a similar structure as follows:

{
    "id" : "1415126969",
    "color"  : "Gold"
    "name":"Random Name 2"
}

and

{
    "id" : "1415166669",
    "color" : "Silver"
    "name":"Random Name"
}

I tried using the aggregation pipeline but I am not sure how to proceed further. Also can anyone suggest a way using Robomongo, that would be nice as well.

回答1:

I would run a simple JavaScript loop for this, since it's going to be quite quick and easy to code. It can also be applied to the same collection:

var ops = [];

db.collection.find().forEach(function(doc) {
  let color;
  Object.keys(doc.color).forEach(function(key) {
    color = doc.color[key];
  });
  ops = [
    ...ops,
    { "updateOne": {
      "filter": { "_id": doc._id },
      "update": { "$set": { "color": color } }
    }}
  ];
  if ( ops.length >= 500 ) {
    db.collection.bulkWrite(ops);
     ops = [];
  }
})

if ( ops.length > 0 ) {
  db.collection.bulkWrite(ops);
   ops = [];
}

So you basically traverse the "keys" of the object and get the value, which later we write back to the document with $set

Alternately, since MongoDB 3.4 you can run an aggregation statement using $objectToArray in order to access the keys. But you really only should do this where the collection has millions of documents. The requirements of $out means the result is in new collection, and not the current one being updated:

db.collecion.aggregate([
  { "$addFields": {
    "color": {
     "$arrayElemAt": [
       { "$map": {
         "input": { "$objectToArray": "$color" },
         "as": "c",
         "in": "$$c.v"
       }},
       0
     ]
    }
  }},
  { "$out": "newcollection" }
])

This works because $objectToArray turns your structure like this:

"color": { "11": "Gold" }

Into this:

"color": [{ "k": "11", "v": "Gold" }]

So we can then use the $map operator in order to extract the "color.v" path value and $arrayElemAt to turn this into a singular value rather than an array.

Generally speaking such conversions would be a lot more complex for aggregation pipeline statements than for what you can achieve simply by manipulating the document structure in code and then writing back to the target collection.

---

Given the existing documents:

{
        "_id" : ObjectId("59389951fc04695e84e7f4ae"),
        "id" : "1415166669",
        "color" : {
                "14" : "Silver"
        },
        "name" : "Random Name"
}
{
        "_id" : ObjectId("59389a75fc04695e84e7f4af"),
        "id" : "1415126969",
        "color" : {
                "11" : "Gold"
        },
        "name" : "Random Name 2"
}

Output from both methods is:

{
        "_id" : ObjectId("59389951fc04695e84e7f4ae"),
        "id" : "1415166669",
        "color" : "Silver",
        "name" : "Random Name"
}
{
        "_id" : ObjectId("59389a75fc04695e84e7f4af"),
        "id" : "1415126969",
        "color" : "Gold",
        "name" : "Random Name 2"
}