Mongo query for newest and oldest data in months in a range of dates

Question

I have in mogo data:

    {_id: 1, value: 4.0, date: ISODate("2024-01-01T00:00:00.000Z")}
    {_id: 2, value: 6.0, date: ISODate("2024-01-03T00:00:00.000Z")}
    {_id: 3, value: 9.0, date: ISODate("2024-01-08T00:00:00.000Z")}
    {_id: 4, value: 11.0, date: ISODate("2024-01-14T00:00:00.000Z")}
    {_id: 5, value: 16.0, date: ISODate("2024-01-28T00:00:00.000Z")}
    {_id: 6, value: 20.0, date: ISODate("2024-02-03T00:00:00.000Z")}
    {_id: 7, value: 24.0, date: ISODate("2024-02-07T00:00:00.000Z")}
    {_id: 8, value: 30.0, date: ISODate("2024-02-19T00:00:00.000Z")}
    {_id: 9, value: 37.0, date: ISODate("2024-02-25T00:00:00.000Z")}
    {_id: 10, value: 40.0, date: ISODate("2024-03-02T00:00:00.000Z")}
    {_id: 11, value: 44.0, date: ISODate("2024-03-06T00:00:00.000Z")}
    {_id: 12, value: 52.0, date: ISODate("2024-03-10T00:00:00.000Z")}
    {_id: 13, value: 60.0, date: ISODate("2024-03-17T00:00:00.000Z")}
    {_id: 14, value: 60.0, date: ISODate("2024-03-26T00:00:00.000Z")}
    {_id: 15, value: 68.0, date: ISODate("2024-03-29T00:00:00.000Z")}
    {_id: 16, value: 40.0, date: ISODate("2024-04-04T00:00:00.000Z")}

I want to receive in query result two values (value nearest the begining of month, and value nearest to the end of month) for each month form range of date. example: I want data form range 2024.01.01-2024.04.30 then I want to receive:

    {_id: 1, value: 4.0, date: ISODate("2024-01-01T00:00:00.000Z")}
    {_id: 5, value: 16.0, date: ISODate("2024-01-28T00:00:00.000Z")}
    {_id: 6, value: 20.0, date: ISODate("2024-02-03T00:00:00.000Z")}
    {_id: 9, value: 37.0, date: ISODate("2024-02-25T00:00:00.000Z")}
    {_id: 10, value: 40.0, date: ISODate("2024-03-02T00:00:00.000Z")}
    {_id: 15, value: 68.0, date: ISODate("2024-03-29T00:00:00.000Z")}
    {_id: 16, value: 40.0, date: ISODate("2024-04-04T00:00:00.000Z")}

Answer 1

You can create an auxiliary field named dayOfMonth using $dayOfMonth to get the respective day in the month. Then, use $setWindowFields to compute the respective ranking in the partition of the month with $dateTrunc. After that, select either ascending ranking or descending ranking. In case of 2 entries sharing the same day, replace $rank with $denseRank to fetch both records.

db.collection.aggregate([
  {
    "$match": {
      date: {
        $gte: ISODate("2024-01-01"),
        $lte: ISODate("2024-04-30")
      }
    }
  },
  {
    "$set": {
      "dayOfMonth": {
        "$dayOfMonth": "$date"
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": {
        "$dateTrunc": {
          "date": "$date",
          "unit": "month"
        }
      },
      "sortBy": {
        "dayOfMonth": 1
      },
      "output": {
        "ascOrder": {
          $rank: {}
        }
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": {
        "$dateTrunc": {
          "date": "$date",
          "unit": "month"
        }
      },
      "sortBy": {
        "dayOfMonth": -1
      },
      "output": {
        "descOrder": {
          $rank: {}
        }
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$or": [
          {
            $eq: [
              1,
              "$ascOrder"
            ]
          },
          {
            $eq: [
              1,
              "$descOrder"
            ]
          }
        ]
      }
    }
  },
  {
    "$unset": [
      "ascOrder",
      "dayOfMonth",
      "descOrder"
    ]
  }
])

Mongo Playground