CODEWITHSUNDEEP
mongodb
andyczerwonka
andyczerwonka

Reputation: 4260

MongoDB latest object within a set query

I have a collection of log data objects that looks something like this.

{
"_id" : ObjectId("52613da744aed3235450e6a9"),
"site" : "5-29",
"date" : ISODate("2013-10-18T10:41:17Z"),
"measures" : [
    [
        "Tank Lvl (ft)",
        6.923
    ],
    [
        "Internal Temp (C)",
        -5.6
    ],
    [
        "External Temp (C)",
        -8.1
    ],
    [
        "Batt (V)",
        12.65
    ],
    [
        "Well Press (psi)",
        31.905
    ]
]
}

Many time per day, the system will collect more data. I'm trying to formulate a query that will return the latest value for a specific day for a specific site. I can easily ask for all the data from a specific site after a specific date, but that gives me all the records for each day. I want only the latest for each site. For example:

Given the following collection: (ignoring the noise...)

1. { "site" : "5-29",   "date" : ISODate("2013-10-18T10:41:17Z"), ....}
2. { "site" : "5-29",   "date" : ISODate("2013-10-18T10:42:17Z"), ....}
3. { "site" : "5-29",   "date" : ISODate("2013-10-18T10:43:17Z"), ....}
4. { "site" : "5-28",   "date" : ISODate("2013-10-18T10:41:17Z"), ....}
5. { "site" : "5-28",   "date" : ISODate("2013-10-18T10:42:17Z"), ....}
6. { "site" : "5-28",   "date" : ISODate("2013-10-18T10:43:17Z"), ....}

I want the query returning only records 3 and 6, because they're the latest entry for that site for that given day.

Upvotes: 1

Views: 87

Answers (2)

Shad
Shad

Reputation: 4464

Use aggregation framework, if you want to find the latest record for each site:

db.collection.aggregate([
  {
    $match: { date: {
      $gt: new ISODate("2013-10-18T00:00:00Z"),
      $lt: new ISODate("2013-10-18T23:59:59Z") }
    }
  },
  {
    $sort: { date: 1 }
  },
  {
    $group: {
      _id: "$site",
      date: { $last: "$date" }
      // ... other document fields
    }
  }
]);
  • At-first, keep only records for specified day
  • Then sort them by datetime
  • And in the end, group records by "site" field and take info from the last document in grouped set.

Upvotes: 2

Shreyance Jain
Shreyance Jain

Reputation: 944

you can run query like this

db.collection.find({ "site" : "5-29", "date" : ISODate("2013-10-18T10:41:17Z")}).sort({_id:-1}).limit(3);

it will return the latest three records of "site" : "5-29", created on "date" : ISODate("2013-10-18T10:41:17Z").

Because ObjectId created by mongodb consist of these parts

  1. 4-byte value representing the seconds since the Unix epoch
  2. 3-byte machine identifier
  3. 2-byte process id, and
  4. 3-byte counter, starting with a random value.

sorting by _id sort records on the basis of time record is created. So reverse sorting gives latest results from table.

Upvotes: 2

Related Questions