MongoDB Aggregation: group over a common field of two arrays

Question

The following is an example document:

{ 'uid': 1, 'sent': [ { 'mid': 100, 'date': 20171210, }, { 'mid': 101, 'date': 20171210, } ], 'open': [ { 'mid': 100, 'date': 20171220, }, { 'mid': 101, 'date': 20171220, } ] } 

I want to group by 'uid' and nested 'mid' fields.
My desired result:

 { 'uid': 1, 'mid': 100, 'sent': [ 20171210 ], 'open': [ 20171220 ] } { 'uid': 1, 'mid': 101, 'sent': [ 20171210 ], 'open': [ 20171220 ] } 

Is there an efficient aggregation method that can give me the result above?

Answer 1

You can $ disable one array, then use the $ filter to save only matching entries in the second array. Then $ unwind the second array and $ group.

 db.temp.aggregate( [ { $unwind: { 'path': '$sent', } }, { $project: { 'uid': 1, 'sent': 1, 'open': { $filter: { input: '$open', as: 'this', cond: { $eq: [ '$sent.mid', '$$this.mid' ] } } } } }, { $unwind: { 'path': '$open', } }, { $group: { '_id': { 'uid': '$uid', 'mid': '$sent.mid' }, 'sent': { '$push': '$sent.date' }, 'open': { '$push': '$open.date' } } }, { $project: { '_id': 0, 'uid': '$_id.uid', 'mid': '$_id.mid', 'sent': 1, 'open': 1 } }, ] );