Project only some fields of array items in sub document

Question

How can I project only particular fields of items in array in sub document?

Consider the following (simplified) example:

{
    "_id" : ObjectId("573d70df080cc2cbe8bf3222"),
    "name" : "Nissan",
    "models" : [
        {
            "name" : "Altima",
            "body" : {
                 "type" : 2,
                 "maxprice" : 31800.00,
                 "minprice" : 21500.00
            }
        },
        {
             "name" : "Maxima",
             "body" : {
                 "type" : 2,
                 "maxprice" : 39200.00,
                 "minprice" : 28800.00
             }
        }
    ]
},
{
    "_id" : ObjectId("80cc2cbe8bf3222573d70df0"),
    "name" : "Honda",
    "models" : [
        {
            "name" : "Accord",
            "body" : {
                "type" : 2,
                "maxprice" : 34100.00,
                "minprice" : 20400.00
            }
        },
        {
            "name" : "Civic",
            "body" : {
                "type" : 3,
                "maxprice" : 27900.00,
                "minprice" : 19800.00
             }
        }
    ]
}

After aggregation, I'd like to get the following output:

{
    "_id" : ObjectId("573d70df080cc2cbe8bf3222"),
    "name" : "Nissan",
    "models" : [
        {
            "type" : 2,
            "minprice" : 21500.00
        },
        {
            "type" : 2,
            "minprice" : 28800.00
        }
    ]
},
{
    "_id" : ObjectId("80cc2cbe8bf3222573d70df0"),
    "name" : "Honda",
    "models" : [
        {
            "type" : 2,
            "minprice" : 20400.00
        },
        {
            "type" : 3,
            "minprice" : 19800.00
        }
    ]
}

So it basically gets all documents, all fields of documents, all items in models array, BUT only some fields of the array items in models. Please help.

Sede · Accepted Answer · 2016-11-25 03:50:54Z

16

You need to $project the "models" field using the $map operator.

db.collection.aggregate([ 
    { "$project": { 
        "name": 1, 
        "models": { 
            "$map": { 
                "input": "$models", 
                "as": "m", 
                "in": { 
                    "type": "$$m.body.type", 
                    "minprice": "$$m.body.minprice" 
                } 
            } 
        }  
    }} 
])

answered Nov 25, 2016 at 3:50

Sede

61.5k20 gold badges158 silver badges162 bronze badges

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

2 Comments

Alex Over a year ago

this is fantastic!

K P Over a year ago

I had to remove ".body" from "type": "$$m.body.type" for it to work. (If anyone faces the same issue)

grodzi · Accepted Answer · 2016-11-24 22:25:26Z

$unwind is your friend

First you can basically filter the (non nested) fields you want.

var projection = {$project:{name:'$name', models:'$models'}};
db.dum.aggregate(projection)

Foreach of your models, you issue a document

var unwindModels = {$unwind:{'$models'}}
db.dum.aggregate(projection, unwindModels)

The idea is that every document issued from your models field will be regrouped later on via the _id field.

Foreach document, you only keep the (sub)fields you want

var keepSubFields = {$project:{name:'$name', type:'$models.body.type', minprice:'$models.body.minprice'}}
db.dum.aggregate(projection, unwindModels, keepSubFields)

Then you reaggregate your models as an array (thanks to the _id of each record which tracks the original record)

var aggregateModels = {$group:{_id:'$_id', name:{$last:'$name'}, models:{$push:{type:'$type', minprice:'$minprice'}}}}
db.dum.aggregate(projection, unwindModels, keepSubFields, aggregateModels)

note1: Here we can use $last because our primary key is not _id but <_id, name>. ($first would be good too)

note2: we refer type by $type, because when you iterate the collection on the aggregateModels stage, your record is of the form <_id, name, type, minprice>

Collectives™ on Stack Overflow

Project only some fields of array items in sub document

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related