MongoDB aggregate pipeline group

Question

I am trying to build a pipeline which will search for documents based on certain criteria and will group certain fields to give desired output. Document structure of deals is

{
   "_id":"123",
   "status":"New",
   "deal_amount":"5200",
   "deal_date":"2018-03-05",
   "data_source":"API",
   "deal_type":"New Business",
   "account_id":"A1"
},
{
   "_id":"456",
   "status":"New",
   "deal_amount":"770",
   "deal_date":"2018-02-11",
   "data_source":"API",
   "deal_type":"New Business",
   "account_id":"A2"
},
{
   "_id":"885",
   "status":"Old",
   "deal_amount":"4070",
   "deal_date":"2017-09-22",
   "data_source":"API",
   "deal_type":"New Business",
   "account_id":"A2"
},

Account name is referenced field. Account document goes like this:

{
   "_id":"A1",
   "name":"Sarah",
},
{
   "_id":"A2",
   "name":"Amber",
},

The pipeline should search for documents whose 'status' is 'New' and 'deal amount' is more than 2000 and it should group by 'account name'. Pipeline i have used goes like this

db.deal.aggregate([{
        $match: {
            status: New,
            deal_amount: {
                $gte: 2000,
            }
        }
    }, {
        $group: {
            _id: "$account_name",
        }
    },{
        $lookup:{
            from:"accounts",
            localField:"account_id",
            foreignField:"_id",
            as:"acc",
        }
    }
])

I want to show fields deal_amount, deal_type, deal_date and account name only in result.

Expected Result:

{
    "_id": "123",
    "deal_amount": "5200",
    "deal_date": "2018-03-05",
    "deal_type": "New Business",
    "account_name": "Sarah"
}, {
    "_id": "885",
    "deal_amount": "4070",
    "deal_date": "2017-09-22",
    "deal_type": "New Business",
    "account_name": "Amber"
},

Do i have to include all the these fields,deal_amount, deal_type, deal_date & account name, in 'group' stage in order to show in result or is there any other ways to do it. Any help is highly appreciated.

IftekharDani · Answer 1 · 2018-03-12T13:42:52.800

Please use this query.

aggregate([{
        $match: {
            status: "New",
            deal_amount: {
                $gte: 2000,
            }
        }
    },
    {
        $lookup:{
            from:"accounts",
            localField:"account_id",
            foreignField:"_id",
            as:"acc",
        }
    },
    {
        $unwind: {
          path: '$acc',
          preserveNullAndEmptyArrays: true,
        },
      },
      {
      $group: {
            _id: "$acc._id",
            deal_amount: { $first: '$deal_amount' },
            deal_date: { $first: '$deal_date' },
            deal_type: { $first: '$deal_type' },

        }
    }
])

You can do by :

1) using $$ROOT reference: link

 { $group : {
            _id : "$author",
            data: { $push : "$$ROOT" }
        }}

2) by assign single parameter

{
        $group: {
            _id: "$account_name",
            deal_amount: { $first: '$deal_amount' },
            deal_date: { $first: '$deal_date' },
            .
            .
        }
    }

craigcaulfield · Answer 2 · 2018-03-13T12:03:43.997

One thing to start with, your $gte operator doesn't work on the string field deal_amount, so you might want to change the field to integers or something similar:

// Convert String to Integer
db.deals.find().forEach(function(data) {
    db.deals.update(
            {_id:data._id},
            {$set:{deal_amount:parseInt(data.deal_amount)}});

Then, to get just the fields you need, reshape the document using $project:

db.deals.aggregate([{
        $match: {
            "status": "New",
            "deal_amount" : {
                "$gte" : 2000 
            }
        }
    },     
     {
         $lookup:{
            from:"accounts",
            localField:"account_id",
            foreignField:"_id",
            as:"acc",
        }
    },
     {
        $project: {
            _id: 1, 
            deal_amount: 1, 
            deal_type: 1, 
            deal_date: 1, 
            "account_name": {"$let":{"vars":{"accl":{"$arrayElemAt":["$acc", 0]}}, in:"$$accl.name"}}
        }
    }    
]);

For me, this produced:

{ 
    "_id" : "123", 
    "deal_amount" : 5200.0, 
    "deal_date" : "2018-03-05", 
    "deal_type" : "New Business", 
    "account_name" : "Sarah"
}

score 0 · Accepted Answer · answered Mar 12 '18 at 13:53

0

Not sure why you need $group stage. You just need to add $project stage to output the account name from the referenced collection.

{
  "$project": {
    "deal_amount": 1, 
    "deal_type": 1, 
    "deal_date": 1, 
    "account_name": {"$let":{"vars":{"accl":{"$arrayElemAt":["$acc", 0]}}, in:"$$accl.name}}
   }
}

answered Mar 12 '18 at 13:53

s7vr

73,656
11
106
127

you are suggesting match, project & lookup stages in the pipeline and in that order. – Ranabir Ray Mar 13 '18 at 06:48
Thanks Veeram but what's the reason for the preferred order. – Ranabir Ray Mar 13 '18 at 12:21
Np. match is always first. project referencing look up fields so lookup goes before project. – s7vr Mar 13 '18 at 12:22
Suppose In my case if i wanted to do grouping, what would the order be $match, $group then $lookup . – Ranabir Ray Mar 13 '18 at 12:44
depends on fields used in group. If you don't use the fields from foreign collection you can do it before lookup else after the lookup. – s7vr Mar 13 '18 at 12:46

score 0 · Answer 4 · answered Oct 17 '21 at 15:17

0

db.deal.aggregate([{$match: {status: {$eq: 'New'}, deal_amount: {$gte: '2000'}}}, {$group: {_id: {accountName: '$account_id', type: '$deal_type', 'amount': '$deal_amount'}}}])

answered Oct 17 '21 at 15:17

Prakash s

159
1
2

2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 17 '21 at 15:24
1

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. You can find more information on how to write good answers in the help center: https://stackoverflow.com/help/how-to-answer . Good luck – nima Oct 17 '21 at 22:19
Also, can you please tidy up your formatting so it's easier to read your solution? See the other answers for good examples. – Jeremy Caney Oct 18 '21 at 02:32

MongoDB aggregate pipeline group

4 Answers4