MongoDB Aggregation - $nin match if value in array

MongoDB aggregation: match the value if it is in the array and show the ones that are not yet.
something similar to what is done in sql

SELECT  *
FROM    common
WHERE   common_id NOT IN
        (
        SELECT  common_id
        FROM    table1 t1
        )

I would like that as an answer show
{
“_id”: 2,
“month”: “FEBRUARY”
}

The simplest I could think is:

lookup = { "$lookup" : {
  "from" : "ventas" ,
  "localField" : "_id" ,
  "foreignField" : "detalles._id" ,
  "as" : "trans"
} }

match = { "$match" : {
  "trans.0" : { "$exists" : false }
} }

pipeline = [ lookup , match ]

mes.aggregate( pipeline )

// will return

{ _id: 2, mes: 'FEBRERO', trans: [] }

But it is somewhat inefficient because we keep looking up and building the array trans which we will not be using except when it has no element. And with the caveat that we might reach the 16Mb limit.

So I tried to modify the lookup stage to:

lookup = { "$lookup" : {
  "from" : "ventas" ,
  "localField" :  "_id" ,
  "foreignField" : "detalles._id" ,
  "pipeline" : [ { "$limit" : 1 } ] ,
  "as" : "trans"
} }

It does the right thing and the array trans has now 0 or 1 element. If I do the lookup only I get the following.

{ _id: 1,
  mes: 'ENERO',
  trans: [ { _id: 1, detalles: [ { _id: 1 } ] } ] }
{ _id: 2, mes: 'FEBRERO', trans: [] }
{ _id: 3,
  mes: 'MARZO',
  trans: [ { _id: 2, detalles: [ { _id: 3 } ] } ] }

Without the $limit:1 pipeline we get the following for MARZO.

{ _id: 3,
  mes: 'MARZO',
  trans: 
   [ { _id: 2, detalles: [ { _id: 3 } ] },
     { _id: 3, detalles: [ { _id: 3 } ] } ] }
1 Like