MongoDB Aggregation query to select document with min and max dates-CodePudding

I have a collection of documents that contains 3 fields DateTime, Score, and Name. I would like to limit data to display only relevant information on mongo charts. Basically what I need is to select document with Minimal date and Maximum date and pass this information to MongoDB charts. Can you please suggest the best way, how can I do this?

Example document:

{
  "_id": {
    "$oid": "62f172b99d3a18179cee4c4c"
  },
  "Name": "pc",
  "Score": 46,
  "DateTime": {
    "$date": {
      "$numberLong": "1659646800000"
    }
  }
},
{
  "_id": {
    "$oid": "62f172b99d3a18179cee4c4c"
  },
  "Name": "pc",
  "Score": 46,
  "DateTime": {
    "$date": {
      "$numberLong": "1649646800000"
    }
  }
}

There are number of these kinds of documents and have different values taken at different time period. So I was able to write simple query to Sort by date and limit to one entry which returns only document with minimal date or maxium. Expected output for mew would be to return both of them

CodePudding user response：

With MongoDB v5.0 , you can use $setWindowFields to compute a rank according to ascending and descending sort of DateTime. Then pick those with rank: 1 to choose the max/min DateTime.

db.collection.aggregate([
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "DateTime": 1
      },
      "output": {
        "minRank": {
          $rank: {}
        }
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "DateTime": -1
      },
      "output": {
        "maxRank": {
          $rank: {}
        }
      }
    }
  },
  {
    "$match": {
      $expr: {
        $or: [
          {
            $eq: [
              "$minRank",
              1
            ]
          },
          {
            $eq: [
              "$maxRank",
              1
            ]
          }
        ]
      }
    }
  },
  {
    // cosmetics
    "$unset": [
      "minRank",
      "maxRank"
    ]
  }
])

Here is the Mongo Playground for your reference.