53

For example, if I have a document like this

{
  a: 1,
  subdoc: {
    b: 2,
    c: 3
  }
}

How can I convert it into a format like this? (without using project)

{
  a: 1,
  b: 2,
  c: 3
}
zachguo
  • 6,200
  • 5
  • 30
  • 31
  • 1
    Why do you insist on doing it without `project`? Please answer this, because your reason might also exclude other possible answers. – Philipp Apr 07 '14 at 07:55
  • 1
    @Philipp There're 30+ elements in the subdocument, so `project` means a lot of typing. – zachguo Apr 08 '14 at 00:56
  • 1
    A good reason could be that the subdocument can change over time and, therefore, specifying 'b' and 'c' is not a good way if, later on, there would be a 'd' in there and you want all the subdocument to go into root. – Rafael Antonio Pólit Feb 12 '16 at 21:02
  • 2
    As of MongoDB 3.4, there's an aggregation pipeline operator named [`$replaceRoot`](https://docs.mongodb.com/manual/reference/operator/aggregation/replaceRoot/), that will allow you to make `subdoc` the new `$$ROOT`. But, it replaces root entirely. I don't know actually how to merge `subdoc` with fields already existing in the original root. Maybe this hints someone... – maganap Jun 22 '17 at 08:49
  • any alternative of $replaceRoot, in version 3.2....mongodb – Prabhat Mishra Nov 02 '18 at 15:20
  • still waiting for an elegant way to achieve this, even I hate projecting all of the fields – virtuvious Feb 12 '19 at 18:29

8 Answers8

45

You can use MongoDB projection i.e $project aggregation framework pipeline operators as well. (recommended way). If you don't want to use project check this link

db.collection.aggregation([{$project{ . . }}]);

Below is the example for your case:

db.collectionName.aggregate
([
    { 
      $project: { 
        a: 1, 
        b: '$subdoc.b', 
        c: '$subdoc.c'
      } 
    }
]);

Gives you the output as you expected i.e.

    {
        "a" : 1,
        "b" : 2,
        "c" : 3
    }
zwl
  • 57
  • 3
  • 5
Amol M Kulkarni
  • 21,143
  • 34
  • 120
  • 164
37

You can use $replaceRoot with a $addFields stage as follows:

db.collection.aggregate([
    { "$addFields": { "subdoc.a": "$a" } },
    { "$replaceRoot": { "newRoot": "$subdoc" }  }
])
chridam
  • 100,957
  • 23
  • 236
  • 235
  • 14
    If you have a lot of fields at root, you can use `mergeObjects` with special variable `$$ROOT` like: `db.collection.aggregate([ {$replaceRoot: { newRoot: { $mergeObjects: ["$$ROOT", "$subdoc"] } } }, {$project: { subdoc: 0 } }, ])` – 0zkr PM Aug 05 '19 at 16:54
25

We can do this with $replaceWith an alias for $replaceRoot and the $mergeObjects operator.

let pipeline = [
   {
      "$replaceWith": {
         "$mergeObjects": [ { "a": "$a" }, "$subdoc" ]
      }
   }
];

or

let pipeline = [
    {
        "$replaceRoot": {
            "newRoot": {
                "$mergeObjects": [{ "a": "$a" }, "$subdoc" ]
            }
        }
    }
];

db.collection.aggregate(pipeline)
styvane
  • 59,869
  • 19
  • 150
  • 156
9

You can also use $$ROOT in $mergeObjects

{ $replaceWith: {
        $mergeObjects: [ "$$ROOT", "$subdoc" ]
} }
daniel
  • 111
  • 1
  • 7
4

Starting Mongo 4.2, the $replaceWith aggregation operator can be used to replace a document by another (in our case by a sub-document) as syntaxic sugar for the $replaceRoot mentioned by @chridam.

We can thus first include within the sub-document the root field to keep using the $set operator (also introduced in Mongo 4.2 as an alias for $addFields) and then replace the whole document by the sub-document using $replaceWith:

// { a: 1, subdoc: { b: 2, c: 3 } }
db.collection.aggregate([
  { $set: { "subdoc.a": "$a" } },
  { $replaceWith: "$subdoc" }
])
// { b: 2, c: 3, a: 1 }
Xavier Guihot
  • 54,987
  • 21
  • 291
  • 190
1

If you want to query nested array element and display it at root level You should use $unwind twice if you have 2 nested array

db.mortgageRequests.aggregate( [
       { $unwind: "$arrayLevel1s" },
       { $unwind: "$arrayLevel1s.arrayLevel2s" },
       { $match: { "arrayLevel1s.arrayLevel2s.documentid" : { $eq: "6034bceead4ed2ce3951f38e" } } },
       { $replaceRoot: { newRoot: "$arrayLevel1s.arrayLevel2s" } }
    ] )
0

I guess the easiest way is altering the result after it returns, using map.

collection.mapFunction = function(el) {
  el.b = el.subdoc.b;
  el.c = el.subdoc.c
  delete(el.subdoc);
  return el;
}

...

var result = await collection.aggregate(...).toArray();
result = result.map(collection.mapFunction);
ariel
  • 15,620
  • 12
  • 61
  • 73
-1

You can do this with the use of $set and $unset

db.collection.update({},{ $set: { "b": 2, "c": 3 }, $unset: { "subdoc": 1 } })

Of course if there are lots of documents in the collection, then much as above you need to loop the collection documents in order to get the values. MongoDB has no way of referring to a value of a field in an update operation alone:

db.collection.find({ "subdoc": {"$exists": 1 } }).forEach(function(doc) {
    var setDoc = {};
    for ( var k in doc.subdoc ) {
        setDoc[k] = doc.subdoc[k];
    }
    db.collection.update(
        { "_id": doc._id },
        { "$set": setDoc, "$unset": "subdoc" }
    );
})

That also employs some safe usage of $exists in order to make sure you are selecting documents where the "subdoc" key is actually there.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
  • 1
    So there's no built-in operator/function in mongoDB to do this, right? – zachguo Apr 07 '14 at 05:15
  • @svg The operators are what I mentioned. You cannot get the value of a field in an update alone, which is also what I said. – Neil Lunn Apr 07 '14 at 05:17
  • 1
    Ok, got it, thanks. I actually meant a single operator, in one shoot. Sorry I didn't say it very clearly. – zachguo Apr 07 '14 at 05:24
  • BTW, creative use of `$set`&`$unset`. I'm afraid looping will be less performant than `$project`, right? If so, I'll stick to `$project`, but do more typing(There're 30+ elements in the subdocument). – zachguo Apr 07 '14 at 05:27
  • 1
    @svg The two things are completely different. You are either manipulating the actual document or you are doing projection to re-shape a particular result, and which can only be done with aggregation. Decide which one you want to do. Alter your document or present results in a different way. – Neil Lunn Apr 07 '14 at 05:32
  • Yes, I'm aware of that. I'm just preparing data for analysis, it's not an issue for me whether or not to alter in-place. Thanks again. – zachguo Apr 07 '14 at 05:56