How do I get the earliest record by date for a unique constraint?

user_82242 0
Viewed 692

I have a collection with two fields that are important to this question: a category and a date (I'll just use "yesterday" and "today" for simplicity).

// Sample Data
{ category: 1, date: ISODate("yesterday") } 
{ category: 1, date: ISODate("today") }
{ category: 2, date: ISODate("yesterday") } 
{ category: 2, date: ISODate("today") }
{ category: 3, date: ISODate("yesterday") }

Now, I'm modifying a query that would first query on the category then sort by date descending and limit to 1 result (giving me the earliest record by date for a single category.

db.collection("things").find({ category: 1 }).sort({ date: -1 }).limit(1)

// Result
{ category: 1, date: ISODate("today") }

That worked great. However, I want the earliest record by for EVERY category. This means I can't use the limit operator (because I don't know how many categories there are at any given point in time) and I'm not guaranteed to have the date a record for every day for each category.

// Expected Result
{ category: 1, date: ISODate("today") }
{ category: 2, date: ISODate("today") }
{ category: 3, date: ISODate("yesterday") }

I've played with distinct, to no success.

How can I get the record with the earliest date for each category in a single query?

mongodb-query mongodb
2 Answers

You can $group by category and use $max to get earliest date:

db.things.aggregate([
    {
        $group: {
            _id: "$category",
            date: { $max: "$date" }
        }
    },
    {
        $project: {
            _id: 0,
            category: "$_id",
            date: 1
        }
    }
])
user_62389770

You can try below aggregation

You can first $sort with date then $group and take the $first document with the date field.

db.collection.aggregate([
  { "$sort": { "date": 1 } },
  { "$group": {
    "_id": "$category",
    "date": { "$first": "$date" }
  }},
  { "$project": { "_id": 0, "category": "$_id", "date": 1 }}
])
user_75106570
Related