Filter nested objects-CodePudding

I have a collection of docs like

{'id':1, 'score': 1, created_at: ISODate(...)}
{'id':1, 'score': 2, created_at: ISODate(...)}
{'id':2, 'score': 1, created_at: ISODate(...)}
{'id':2, 'score': 20, created_at: ISODate(...)}

etc.

Does anyone know how to find docs that were created within the past 24hrs where the difference of the score value between the two most recent docs of the same id is less than 5?

So far I can only find all docs created within the past 24hrs:

[{
    $project: {
        _id: 0,
        score: 1,
        created_at: 1
    }
}, {
    $match: {
        $expr: {
            $gte: [
                '$created_at',
                {
                    $subtract: [
                        '$$NOW',
                        86400000
                    ]
                }
            ]
        }
    }
}]

Any advice appreciated.

CodePudding user response：

If I've understood correctly you can try this query:

First the $match as you have to get documents since a day ago.
Then $sort by the date to ensure the most recent are on top.
$group by the id, and how the most recent were on top, using $push will be the two first elements in the array.
So now you only need to $sum these two values.
And filter again with these one that are less than ($lt) 5.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gte: [
          "$created_at",
          {
            $subtract: [
              "$$NOW",
              86400000
            ]
          }
        ]
      }
    }
  },
  {
    "$sort": {
      "created_at": -1
    }
  },
  {
    "$group": {
      "_id": "$id",
      "score": {
        "$push": "$score"
      }
    }
  },
  {
    "$project": {
      "score": {
        "$sum": {
          "$firstN": {
            "n": 2,
            "input": "$score"
          }
        }
      }
    }
  },
  {
    "$match": {
      "score": {
        "$lt": 5
      }
    }
  }
])

Example here

Edit: $firstN is new in version 5.2. Other way you can use $slice in this way.

CodePudding user response：

If I understand you correctly, you want something like:

db.collection.aggregate([
  {$match: {$expr: {$gte: ["$created_at", {$subtract: ["$$NOW", 86400000]}]}}},
  {$sort: {created_at: -1}},
  {$group: {_id: "$id", data: {$push: "$$ROOT"}}},
  {$project: {pair: {$slice: ["$data", 0, 2]}, scores: {$slice: ["$data.score", 0, 2]}}},
  {$match: {$expr: {
        $lte: [{$abs: {$subtract: [{$first: "$scores"}, {$last: "$scores"}]}}, 5]
  }}},
  {$unset: "scores"}
])

See how it works on the playground example