CODEWITHSUNDEEP
databasemongodbmongodb-querynosqlaggregation-framework
Eitank
Eitank

Reputation: 692

mongo group by a value that can be present in multiple fields

I am trying to create a query to group by a value that can be present in 2 fields. let me explain what i mean by that.

lets say i have a trade and this trade document looks like this:

{
 date:20/05/2020,
 buyer:"papa",
 seller:"mama",
}

I want to group by month and the trades that papa participated in on either the seller or the buyer side.

this query gives me only a group of {month,seller}, how can i make it that he can be either in buyer or seller ?

db.getCollection('trades').aggregate([
  {$group: {
      _id: { month:{$month: "$date"}, participant:"$seller"}, 
      numberoftrades: {$sum: 1} 
  }}
]);

Upvotes: 1

Views: 131

Answers (1)

mickl
mickl

Reputation: 49985

You need to put buyer and seller into one array and then $unwind that array so that same person can be grouped being either side of the transaction:

db.collection.aggregate([
    {
        $project: {
            month: { $month: "$date" },
            participants: [ "$buyer", "$seller" ] 
        }
    },
    { $unwind: "$participants" },
    {
        $group: {
            _id: { month: "$month", participants: "$participants" },
            count: { $sum: 1 }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions