'How to find items in a collections which are not in another collection with MongoDB

I want to query my mongodb to perform a non-match between 2 collections. Here is my structure :

CollectionA :

_id, name, firstname, website_account_key, email, status

CollectionB :

_id, website_account_key, lifestage, category, target, flag_sistirt

I'am trying to find Items in B, for which, there is no lines in A (website_account_key is unique and allows to find elements in B for each A [one-to-one])

I tried to do :

dataA_ids = db.dataA.find().map(function(a){return a.website_account_key;})

db.dataB.find( { website_account_key: { $nin: [dataA_ids] } } )

But i have nothing unless, i add some test data who are supposed to work :(

Here is whole code of my test :

db.products.insert( { item: "card", qty: 15, code:100 } )
db.products.insert( { item: "pass", qty: 15, code:230 } )
db.products.insert( { item: "badge", qty: 15, code:543 } )
db.products.insert( { item: "passX", qty: 15, code:876 } )

db.references.insert( { code:230, ref:"AAZRT"})
db.references.insert( { code:888, ref:"RUBFE"})
db.references.insert( { code:876, ref:"ERHAA"})
db.references.insert( { code:120, ref:"DRETAR"})

codeInProducts = db.products.find().map(function(a){return a.code;})
db.references.find( { code: { $nin: [codeInProducts] } } )

My goal is to retrieves lines in references for which i dont find elements in products (lines in references not found in products (code is link))



Solution 1:[1]

Since mongodb version 3.6, you can do it with one query only, using $lookup:

db.dataA.aggregate([
  {
    $group: {
      _id: 0,
      codes: {$addToSet: "$code"}
    }
  },
  {
    $lookup: {
      from: "dataB",
      let: {codes: "$codes"},
      pipeline: [
        {$match: {$expr: {$not: {"$in": ["$code", "$$codes"]}}}}
      ],
      as: "codes"
    }
  },
  {
    $unwind: "$codes"
  },
  {
    $replaceRoot: {
      "newRoot": "$codes"
    }
  }
])

As can be seen on this playground example

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 nimrod serok