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?

Answer 1

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.

Answer 2

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
    }
}