CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
chrispytoes
chrispytoes

Reputation: 1889

Filter the foreign documents on $lookup

I have a one-to-many relationship between devices and events. I am trying to query devices, while joining to each one all of its events, which also must match another condition.

Here are the relevant properties of each table:

devices:

{
  networkId: String,
  serial: String // Unique device ID.
  ...
}

events: 

{
  deviceSerial: String, // The "serial" field of the device this event is for.
  ts: Number // Unix epoch timestamp in seconds.
  ...
}

I found the 3.6 syntax for $lookup to do exactly what I want, however it doesn't seem to be working. I am getting the correct devices, but the events array always comes back empty, but I know for a certain fact that there are events which match the conditions.

My pipeline:

[
  {
    $match: {
      networkId: 'N_660903245316632251'
    }
  },
  {
    $lookup: {
      from: 'events',
      let: {'deviceSerial': '$deviceSerial'},
      pipeline: [
        $match: {
          deviceSerial: '$$deviceSerial',
          ts: {$gte: 1556686800, $lte: 1559365199}
        }
      ]
    }
  }
]

Results successfully come back with selected devices, but the events array is always empty:

[
  {
    ...
    events: []
  }
  ...
]

Upvotes: 1

Views: 53

Answers (1)

mickl
mickl

Reputation: 49945

One thing you're missing here is that you need to use expressions ($expr) to refer to variables that are defined by let statement:

{
    $lookup: {
        from: 'events',
        let: {'deviceSerial': '$deviceSerial'},
        pipeline: [
            $match: {
                {
                    $expr: {
                        $and: [
                            { $eq: [ "$deviceSerial", "$$deviceSerial" ] },
                            { $gte: [ "$ts", 1556686800 ] },
                            { $lte: [ "$ts", 1559365199] }
                        ]
                    }
                }
            }
        ] 
    }
}

Upvotes: 1

Related Questions