MongoDB Aggregation : Group on common field of two arrays

Question

Below is a sample 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 on 'uid' and nested 'mid' fields.
My desired output is :

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

Is there any efficient way of Aggregation which can give me above result?

Answer 1

You can $unwind the one array, then use $filter to keep only the 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
            }
        },
    ]
);