How to correctly perform join but with math operations in MongoDB?-CodePudding

Here I have a collection, say test, storing data with a field named timestamp (in ms). Documents in this collection are densely inserted with timestamp interval 60000. That's to say, I can always find one and only one document whose timestamp is 1 minute before that of a refered one (except for the very first one, of course). Now I want to perform a join to correlate each document with that whose timestamp is 1 minute before. I've tried this aggregation:

...
$lookup : {
  from: 'test',
  let : { lastTimestamp: '$timestamp'-60000 },
  pipeline : [
    {$match : {timestamp:'$timestamp'}}
  ],
  as: 'lastObjArr'
},
...

which intends to find the array of the very document and set it as the value of key lastObjArr. But in fact lastObjArr is always an empty one. What happend?

CodePudding user response：

you defined a variable called "lastTimestamp" and you assign it with

'$timestamp'-60000 But you never use it, change your code as following it should work:

$lookup : {
  from: 'test',
  let : { lastTimestamp: '$timestamp'-60000 },
  pipeline : [
    {$match : {timestamp:'$$lastTimestamp'}}
  ],
  as: 'lastObjArr'
},

CodePudding user response：

Your $lookup pipeline is incomplete as it's missing the necessary math operators. For a start, lastObjArr is empty due to a number of factors, one of them being that the expression

let : { lastTimestamp: '$timestamp'-60000 },

doesn't evaluate correctly, it needs to use the $subtract operator

let : { lastTimestamp: { $subtract: ['$timestamp', 60000] } },

Also, the $match pipeline step needs to use the $expr operator together with $eq for the query to work, i.e.

$lookup : {
  from: 'test',
  let : { lastTimestamp: { $subtract: ['$timestamp', 60000] } },
  pipeline : [
    { $match : { 
      $expr: { $eq: ['$timestamp', '$$lastTimestamp'] }
    } }
  ],
  as: 'lastObjArr'
}