Question 116184 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Filter nested objects

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.

Edit: By the two most recent docs, the oldest of the two can be created more than 24hrs ago. So the most recent doc would be created within the past 24hrs, but the oldest doc could be created over 24hrs ago.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    EDIT:
    according to you comment, one option is:

    db.collection.aggregate([
  {$setWindowFields: {
      partitionBy: "$id",
      sortBy: {created_at: -1},
      output: {data: {$push: "$$ROOT", window: {documents: ["current", 1]}}}
  }},
  {$group: {
      _id: "$id",
      created_at: {$first: "$created_at"},
      pair: {$first: "$data"}
  }},
  {$match: {$expr: {$and: [
          {$gte: ["$created_at", {$dateAdd: {startDate: "$$NOW", unit: "day", amount: -1}},
          {$eq: [{$size: "$pair"}, 2]},
          {$lte: [{$abs: {$subtract: [{$first: "$pair.score"},
                    {$last: "$pair.score"}]}}, 5]}
  ]}}},
  {$project: {_id: 0, pair: 1}}
])

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search