MongoID join on embedded documents

Question

We have a Shipment document with an embedded ShipmentEvent document as in...

class Shipment
  include Mongoid::Document
  embeds_many :shipment_events
end

class ShipmentEvent
  include Mongoid::Document
  embedded_in :shipment
end

What we need to get is a join containing shipments and theirs latest shipment_event based on a :event_at field.

Example data (simplified with just one shipment):

[
  {
    "_id" => BSON::ObjectId('1'),
    "shipment_events" => [
      {
        "_id" => BSON::ObjectId('2'),
        "event_at" => 2022-04-09 15:00:00 UTC
      },
      {
        "_id" => BSON::ObjectId('3'),
        "event_at" => 2022-04-09 18:00:00 UTC
      }
    ]
  }
]

The result expected should contain the shipment and only the second (latest by :event_at) shipment_event. Something like...

[
  {
    "_id" => BSON::ObjectId('1'),
    "_event_id" => BSON::ObjectId('3'),
    "event_at" => 2022-04-09 18:00:00 UTC
  }
]

I know join queries are not possible with MongoId and we will need to use aggregation I guess. Any ideas will be appreciated.

Thank you.

Answer 1

You can do it this way:

unwind = { "$unwind" => "$shipment_events" }
sort = {
  "$sort" => {
    "_id" => 1, "shipment_events.event_at" => -1
  }
}
group = {
  "$group" => {
    "_id" => "$_id",
    "event_id" => {
      "$first" => "$$ROOT.shipment_events._id"
    },
    "event_at" => {
      "$first" => "$$ROOT.shipment_events.event_at"
    }
  }
}

Shipment.collection.aggregate([unwind, sort, group])

Please find playground example below.
https://mongoplayground.net/p/oLTG-18aIDm