Flatten a nested JSON structure in mongoDB

Question

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?

Why not just flatten it on the client? There's no practical way to flatten the fields in MongoDB efficiently with all sizes of collections. — WiredPrairie
– WiredPrairie, Commented Jul 30, 2013 at 4:31
What do you mean by flattening on the client? How can I do that? — Vinay Pandey
– Vinay Pandey, Commented Jul 30, 2013 at 4:35
Use whatever programming language you plan on using and do your own client side projection of the data. — WiredPrairie
– WiredPrairie, Commented Jul 30, 2013 at 5:44
Does this answer your question? MongoDB - $project nested document to root level — nimrod serok
– nimrod serok, Commented May 12, 2022 at 7:02

llx · Accepted Answer · 2013-07-30 02:50:51Z

36

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/

answered Jul 30, 2013 at 2:50

llx

5255 silver badges7 bronze badges

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

3 Comments

Vinay Pandey Over a year ago

I get this: "errmsg" : "exception: aggregation result exceeds maximum document size (16MB)"

Dylan Tong Over a year ago

The aggregation framework currently returns the results as a single document as a result, the result is limited to the 16MB document size limit. The next release should include the ability to pipe the results into a collection eliminating this limitation. In the meantime, writing a MapReduce process is the way to go if you encounter the 16MB limit. If you only happen to need to retrieve a few documents at a time, you should start your pipe with a $match operator and/or use a $limit to possibly work within the 16MB limit.

Vinay Pandey Over a year ago

I am struggling with writing a map/reduce function on MongoVUE. Any hint regarding that will be greatly appreciated.

nimrod serok · Accepted Answer · 2023-10-04 21:30:01Z

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

Derick · Accepted Answer · 2013-07-30 08:06:00Z

2

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.

answered Jul 30, 2013 at 8:06

Derick

36.9k7 gold badges82 silver badges104 bronze badges

Collectives™ on Stack Overflow

Flatten a nested JSON structure in mongoDB

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related