I have documents that look like:

[ 
  { 
    value: 'Apple',
    createdAt: '2021-12-09T20:15:26.421 00:00',
  },
  { 
    value: 'Blueberry',
    createdAt: '2021-12-09T20:45:26.421 00:00',
  },
  { 
    value: 'Cranberry',
    createdAt: '2021-12-09T21:30:26.421 00:00',
  },
  { 
    value: 'Durian',
    createdAt: '2022-01-24T20:15:26.421 00:00',
  },
  { 
    value: 'Elderberry',
    createdAt: '2022-01-24T20:45:26.421 00:00',
  },
]

I'd like to do an aggregation where I get the oldest document, with the caveat that if another document was created within an hour, it invalidates the first document and I actually want to grab that one instead. For example, in the above I would like to return Cranberry. Initially pick Apple, but since Blueberry comes within an hour, move to that one, and since Cranberry comes within an hour of Blueberry, select Cranberry.

CodePudding user response：

You can do the followings in an aggregation pipeline:

1. $sort by createdAt
2. $limit to get the oldest document
3. $lookup to get all the documents with createdAt behind the current document
4. $reduce to loop the result array; update the accumulator/result only if the current entry is within 1 hour

db.collection.aggregate([
  {
    $sort: {
      createdAt: 1
    }
  },
  {
    $limit: 1
  },
  {
    "$lookup": {
      "from": "collection",
      let: {
        current: "$createdAt"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $gte: [
                "$createdAt",
                "$$current"
              ]
            }
          }
        }
      ],
      "as": "within"
    }
  },
  {
    "$addFields": {
      "within": {
        "$reduce": {
          "input": "$within",
          "initialValue": null,
          "in": {
            "$cond": {
              "if": {
                $or: [
                  {
                    $eq: [
                      "$$value",
                      null
                    ]
                  },
                  {
                    $lte: [
                      {
                        "$subtract": [
                          "$$this.createdAt",
                          "$$value.createdAt"
                        ]
                      },
                      3600000
                    ]
                  }
                ]
              },
              "then": "$$this",
              "else": "$$value"
            }
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.