CODEWITHSUNDEEP
mongodb
Vinay Pandey
Vinay Pandey

Reputation: 1179

Flatten a nested JSON structure in mongoDB

I have an object stored in MongoDB which looks like:

{
_id: 123
name: "xyz"
    attrib: 
    {
       address: "123 xyz rd",
       phone: "123-456-7890"
    }
}

I want to flatted this structure, so that there is no attrib field, I just have address and phone field along with name and _id.

So far, this is what I've tried:

db.emp.aggregate(
    { 
    $project : {
            { addr : '$review.attrib.address' },
            { phn : '$review.votes.phone' },
        }
    }
);

Can anyone help me further?

Upvotes: 24

Views: 22250

Answers (3)

nimrod serok
nimrod serok

Reputation: 16033

Since mongodb version 4.2, you can use one update with aggregation pipeline of $set and $unset.

The short and literal option is:

db.collection.update({},
  [
    {$set: {
        address: "$attrib.address",
        phone: "$attrib.phone"
    }},
    {$unset: "attrib"}
  ],
  {multi: true}
)

playground - literal

While the generic option, if you don't wan to specify all inner field, can be:

db.collection.update({},
[
  {$replaceRoot: {newRoot: {$mergeObjects: ["$$ROOT", "$attrib"]}}},
  {$unset: "attrib"}
],
{multi: true})

See how it works on the playground example

For older versions you can simply use $rename/$set and $unset:

($rename is just a combination of $set and $unset)

db.collection.update({},
{
  $rename: {
    "attrib.address": "address",
    "attrib.phone": "phone"
  }
}, {multi: true})

As you can see here

And later:

db.collection.update({}, {$unset: {attrib: ""}}, {multi: true})

As you can see here

Upvotes: 8

Derick
Derick

Reputation: 36784

If you are intending to change all the documents in the database, then neither the Aggregation Framework or Map/Reduce are they way to go. You instead write a script in your favourite language and loop over all the documents in the collection to modify them one by one.

Upvotes: 2

llx
llx

Reputation: 425

I tried it： 

db.abc.insert({
  _id: 123,
  name: "xyz",
  attrib: {
     address: "123 xyz rd",
     phone: "123-456-7890"
  }
});
db.abc.aggregate(
{ 
  $project : {
    _id:1,
    name:1,
    addr : '$attrib.address',
    phn : '$attrib.phone' 
  }
}
);

More detail, you can see：use $project to rename fields http://docs.mongodb.org/manual/reference/aggregation/project/

Upvotes: 36

Related Questions