Aggregate/project sub-document as top-level document in mongo

Question

After a few aggregation steps (pipeline steps) in one of my collections, I'm ending up with the following result:

{
    "_id" : ObjectId("574e7722bffe901713d383bb"),
    "eventname" : "Ball Passed",
    "command" : {
        "_id" : ObjectId("57ec6b6f6c61e919b578fe7c"),
        "name" : "Run",
        "strike" : 15,
        "score" : true,
        "duration" : 123
    }
}
{
    "_id" : ObjectId("57ec6b6f6c61e919b578ff8a"),
    "eventname" : "Ball Passed",
    "command" : {
        "_id" : ObjectId("573d688d080cc2cbe8aecbbc"),
        "name" : "Run",
        "strike" : 12,
        "score" : false,
        "duration" : 597
    }
}

Which is fine!

However, in the next step of the aggregation, I'd like to get the following result:

{
    "_id" : ObjectId("57ec6b6f6c61e919b578fe7c"),
    "name" : "Run",
    "strike" : 15,
    "duration" : 123
}
{
    "_id" : ObjectId("573d688d080cc2cbe8aecbbc"),
    "name" : "Run",
    "strike" : 12,
    "duration" : 597
}

If you have noticed, the command field should become the top-level document, and command.score should be skipped.

How can I achieve this in a single step? If that is not possible in a single step, then in multiple steps? I guess I've to use $project?

score 26 · Answer 1 · answered Apr 14 '17 at 12:56

When you have many, many fields in the sub-document and occasionally it is updated with new fields, then projection is not a viable option. Fortunately, since 3.4, MongoDB has a new operator called $replaceRoot.

All you have to do is add a new stage at the end of your pipeline.

db.getCollection('sample').aggregate([
    {
        $replaceRoot: {newRoot: "$command"}
    },
    {
        $project: {score: 0 } //exclude score field
    }
])

This would give you the desired output.

Note that in case of aggregation (especially after a $group stage) the 'command' document could be an array and could contain multiple documents. In this case you need to $unwind the array first to be able to use $replaceRoot.

score 14 · Accepted Answer · edited Jun 20 '20 at 09:12

As you have guessed, $project allows you to do that:

db.col.aggregate([
{
    $project : 
    {
        _id: "$command._id",
        name: "$command.name", 
        strike: "$command.strike", 
        duration: "$command.duration"
    }
}
]).pretty()

I inserted your previous results and the above query returned this:

{
    "_id" : ObjectId("57ec6b6f6c61e919b578fe7c"),
    "name" : "Run",
    "strike" : 15,
    "duration" : 123
}
{
    "_id" : ObjectId("573d688d080cc2cbe8aecbbc"),
    "name" : "Run",
    "strike" : 12,
    "duration" : 597
}

So piping your query with this $product should produce the result you are looking for.

Update after comments

If the exact structure is not your main concern, but rather the exclusion of few fields (wihtout having to list all fields to include), then you may use find() instead of aggregate().

~~aggregate's product only lets you exclude _id. This means you need to manually list all fields to include.~~
Note: Since version 3.4 of MongoDB it is possible to exclude fields in $project phase (https://docs.mongodb.com/manual/reference/operator/aggregation/project/#exclude-fields)

find however, lets you list the fields to hide.

Alternative

(1) You could redirect your aggregate result to another collection using $out :

{ $out : "commands" }

(2) Even though the structure won't be exactly as you'd like, you'll then be able to do a find query and hide fields:

db.commands.find({}, {_id:0, "command.score":0, eventname:0}).pretty()

It returns this, which is pretty close to what you were looking for:

{
    "command" : {
        "_id" : ObjectId("57ec6b6f6c61e919b578fe7c"),
        "name" : "Run",
        "strike" : 15,
        "duration" : 123
    }
}

In question, I've simplified my documents. I actually have over 70 fields in `command` sub-document. Should I project every single field like above? Is there any other way to project all fields but skip only 1 field? — Zafar, Oct 11 '16 at 01:40
I belive aggregate will only let you hide _id. Otherwise, the logics only works in "list of fields to display". You could also use $out to redirect the result, then use a find(), which works the opposite way: it lets you list fields to hide.. — alexbt, Oct 11 '16 at 02:03
I updated the answer (section "Update after comments) with a suggestion — alexbt, Oct 11 '16 at 02:12
Since MongoDB 3.4 `$project` can exclude fields: https://docs.mongodb.com/manual/reference/operator/aggregation/project/#exclude-fields — SteveB, Mar 27 '18 at 13:55

score 0 · Answer 3 · answered Jun 09 '19 at 16:38

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 $replaceRoot.

// { "eventname": "Ball Passed", "command": { "_id": "57e...", "name": "Run", "strike": 15, "score": true,  "duration": 123 } }
// { "eventname": "Ball Passed", "command": { "_id": "573...", "name": "Run", "strike": 12, "score": false, "duration": 597 } }
db.collection.aggregate([
  { $replaceWith: "$command" }, // replaces the document by the content of "command"
  { $unset: ["score"] }         // drops the "score" field
])
// { "_id" : "57e...", "name" : "Run", "strike" : 15, "duration" : 123 }
// { "_id" : "573...", "name" : "Run", "strike" : 12, "duration" : 597 }

Also note the $unset aggregation operator also introduced in Mongo 4.2, as an alternative syntax for $project when used to only drop fields.

Aggregate/project sub-document as top-level document in mongo

3 Answers3

Update after comments

Alternative

Linked