CODEWITHSUNDEEP
mongodbaggregation-framework
JoeWemyss
JoeWemyss

Reputation: 607

mongo $project not projecting original values

I am new to Mongodb, and NoSQL in general and I am trying to use mongodbs aggregate function to aggregate data from one collection to be inserted into another. An example of the original collection would be this:

Original Collection { supplier: 'aldi', timestamp: '1492807458', user: '[email protected]', hasBeenAggregated:false, items:[{ name: 'butter', supplier: 'aldi', expiry: '1492807458', amount: 454, measureSymbol: 'g', cost: 2.19 },{ name: 'milk', supplier: 'aldi', expiry: '1492807458', amount: 2000, measureSymbol: 'ml', cost: 1.49 }] }

An example of the output I am trying to achieve would be:

New Collection { user:'[email protected]', amount: 3.68, isIncome: false, title: 'food_shopping', timestamp: '1492807458' }

The aggregation function that I am using is:

Aggregation var result = db.runCommand({ aggregate: 'food_transactions', pipeline: [ {$match: {hasBeenAggregated: false}}, {$unwind: '$items'}, {$group:{_id: '$_id',amount:{$sum: '$items.cost'}}}, {$project: { _id:0, user:1, amount:1, isIncome: {$literal: false}, title:{$literal: 'food_shopping'}, timestamp:1 }} ] }); printjson(result)

This aggregation function does not return the user or timestamp fields. Instead, I get the following output:

Output { "amount" : 3.6799999999999997, "isIncome" : false, "title" : "food_shopping" }

If I don't group the results and perform the calculations in the $project stage, the fields are all projected correctly, but obviously, there is a new document created for each sub-document in the items array and that rather defeats the purpose of the aggregation.

What am I doing wrong?

Upvotes: 0

Views: 685

Answers (1)

s7vr
s7vr

Reputation: 75934

Update your $group pipeline to include all the fields you wish to project further down the pipeline.

To include user field you can use $first

{$group:{_id: '$_id', user:{$first:'$user`}, amount:{$sum: '$items.cost'}}},

Additionally, if you are 3.4 version you can simplify your aggregation to below.

Use $reduce to sum all the item's cost in a single document. For all documents you can add $group after $reduce.

db.collection.aggregate([
        {$match: {hasBeenAggregated: false}},
        {$project: {
            _id:0,
            user:1,
            amount: {
              $reduce: {
                input: "$items",
                initialValue: 0,
                in: { $add : ["$$value", "$$this.cost"] }
              }
            },
            isIncome: {$literal: false},
            title:{$literal: 'food_shopping'},
            timestamp:1
        }}
    ])

Upvotes: 2

Related Questions