Aggregation with multiple sorts then groups

Working with Data
1

Hello,

So I’m kind of new to Mongo aggregations and after a lot of researching and googling I can’t figure out how to do a particular query.

Say I have many documents with the following format:

{
  _id: 1,
  client: "Some Client",
  type: "Some Type",
  firstUsed: 2021-01-05T13:23:37.000+0000
  lastUsed: 2021-05-05T18:11:23.000+0000
}

What I’m trying to do is group all the documents by type (for a particular client), and then get the first firstUsed datetime and the last lastUsed datetime.

I’ve been playing with aggregations but after doing a $match on the client, $group on the type, I can only figure out then how to get only one of the dates I need.

Sorting by firstUsed and getting the first document is fine, but then getting the last document doesnt mean I’m getting the correct lastUsed.

How do I re-sort and get the last lastUsed datetime while keeping the already found firstUsed?

Hope that makes sense,

Thanks

2

Look at

and have one facet that does the lastUsed and another one for firstUsed. I am not too sure if you need to $group before $facet or $group inside each $facet. If you could supply a few more documents it might help to test.

3

Hello @Doto_Pototo, welcome to the MongoDB Community forum!

I can’t clearly understand what you mean by “… get the first firstUsed datetime and the last lastUsed datetime”. You can try using the Aggregation operator $max to get the highest of the lastUsed and $min for firstUsed in the $group stage of the pipeline.

As @steevej mentioned you can also try using $facet.

1 Like