CODEWITHSUNDEEP
mongodbmongodb-query
developthou
developthou

Reputation: 363

MongoDB - How do join between the two collections by getting only one document from the second collection?

I am using Mongo 3.4.24. I have two collections - health, health_history.

I keep health of the different nodes in the document in the health collection and during each snapshot I move the older documents to health_history

health

{
    "_id": ObjectId("61857adabe73638f9c8bca19"),
    "name": "Test",
    "snapshot_time": "2021-11-05 11:41:28",
    "status": "DOWN",
    "create_time": ISODate("2021-11-05T18:41:30.468Z")
}

health_history - which keeps the older documents from health collection

I want to find how long Test has been DOWN for by finding the last time when the status was UP.

The challenge is there are multiple documents for Test in health_history. I need to find the latest create_time from the document in health_history when it was UP and calculate the time difference (in minutes) with the document in health collection and create a new key duration

Something like

{
    "_id": ObjectId("61857adabe73638f9c8bca19"),
    "name": "Test",
    "snapshot_time": "2021-11-05 11:41:28",
    "status": "DOWN",
    "create_time": ISODate("2021-11-05T18:41:30.468Z"),
    "duration": "30"

}

How do I do this in Mongo?

Upvotes: 0

Views: 43

Answers (1)

ray
ray

Reputation: 15215

You could do the followings in an aggregation pipeline:

  1. $lookup health_history
  2. $match to keep status:"UP" records only
  3. $sort by create_time of the hist document
  4. $limit: 1 to get the last "UP" document
  5. use $subtract to get the date difference as you are using Mongo 3.4. $divide the result by 60000(60s * 1000ms = 1 minute) to get the difference in minute interval
db.health.aggregate([
  {
    $match: {
      "name": "Test"
    }
  },
  {
    "$lookup": {
      "from": "health_history",
      "localField": "name",
      "foreignField": "name",
      "as": "hist"
    }
  },
  {
    "$unwind": "$hist"
  },
  {
    $match: {
      "hist.status": "UP"
    }
  },
  {
    $sort: {
      "hist.create_time": -1
    }
  },
  {
    $limit: 1
  },
  {
    $addFields: {
      duration: {
        "$divide": [
          {
            "$subtract": [
              "$create_time",
              "$hist.create_time"
            ]
          },
          // 60s * 1000ms = 1 minute
          60000
        ]
      }
    }
  }
])

Here is the Mongo playground for your reference.

Upvotes: 1

Related Questions