MongoDB join or lookup via nested/sub document field

Question

I have variety of events, and hourly statistics of receiving that event. I would like to merge both of them using $lookup. Please help me, on how do to that on nested document where event_id is my common field to lookup.

Events:
{
  "event_id"      : "1",     //Unique
  "event_name"    : "name1",
  "severity"      : "high",
  "type"          : "error"
}

Hourly_stats:  
{
   "hour"         : "2017O30123" //23rd hour
   incoming_events_during_this_hour : [
        {"event_id" : 1, "received" : 12},
        {"event_id" : 2, "received" : 14}, 
        {"event_id" : 3, "received" : 8},
   ]
}
{
   "hour"         : "2017O30124" //24th hour
   incoming_events_during_this_hour : [
        {"event_id" : 1, "received" : 16},
        {"event_id" : 2, "received" : 14}, 
        {"event_id" : 3, "received" : 8},
   ]
}

Expected output is:

After Lookup on Events with target collection Hourly_stats
    {
      "event_id"      : "1"
      "event_name"    : "name1",
      "severity"      : "high",
      "type"          : "error"
      incoming_events_during_this_hour : [
              {
               "hour"         : "2017O30123",
               {"event_id" : 1, "received" : 12},
              },
              {
               "hour"         : "2017O30124",
               {"event_id" : 1, "received" : 16},
              }
      ]
    }

OR as multiple rows (per hour basis)

    {
      "event_id"      : "1"
      "event_name"    : "name1",
      "severity"      : "high",
      "type"          : "error"
      incoming_events_during_this_hour : 
              {
               "hour"         : "2017O30123",
               {"event_id" : 1, "received" : 12},
              }
    }


    {
      "event_id"      : "1"
      "event_name"    : "name1",
      "severity"      : "high",
      "type"          : "error"
      incoming_events_during_this_hour : 
              {
               "hour"         : "2017O30124",
               {"event_id" : 1, "received" : 16},
              }
    }

Answer 1

You can try below aggregation for a start.. You will need to add a match on hour as it will fetch the entire Hourly_stats collection.

The below query unwinds the incoming_events_during_this_hour to get the event id and groups the incoming_events_during_this_hour for each event and final step is to do lookup for event information.

db.Hourly_stats.aggregate([{
    $unwind: "$incoming_events_during_this_hour"
}, {
    $group: {
        _id: "$incoming_events_during_this_hour.event_id",
        incoming_events_during_this_hour: {
            $push: {
                hour: "$hour",
                "event_received": "$incoming_events_during_this_hour"
            }
        }
    }
}, {
    $lookup: {
        "from": "Events",
        "localField": "_id",
        "foreignField": "event_id",
        "as": "_id"
    }
}, {
    $project: {
        _id: 0,
        event: {
            $arrayElemAt: ["$_id", 0]
        },
        incoming_events_during_this_hour: 1
    }
}]);