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

Question

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))

It doesn't seem like we can "help" unless you can provide an "example" of something that "should work" but actually "fails". It does not seem like there is something wrong with the basic logic, but rather there is something wrong with your actual code. I feel that in the construction of your question you have failed to point to your "actual code" which is likely the real source of your problem. — Neil Lunn, Mar 16 '15 at 12:04
Hello, here is my sample code that doesnt work : http://pastebin.com/WxgPfjYs — Damien, Mar 16 '15 at 12:50
@Damien: Please include the code here. You might want to read [How do I ask a good question](http://stackoverflow.com/help/how-to-ask), which enhances the probability for getting a useful answer _drastically_. You might find [ESR](https://en.m.wikipedia.org/wiki/Eric_S._Raymond)'s excellent essay [How To Ask Questions The Smart Way](http://catb.org/~esr/faqs/smart-questions.html) helpful, too. — Markus W Mahlberg, Mar 16 '15 at 12:54
@Damien You should really have "Edited your question". But your code does return the 4 lines as it should. Why would you think it should do different? Edit your question by both putting your example in the content and explaining why you think there should be a different result to the rest of us kids. Or otherwise take the time to realize what you are doing wrong here. — Neil Lunn, Mar 16 '15 at 12:59
@Damien as per my understanding I think you should try this `db.references.find( { code: { $nin: codeInProducts } } ) ` it shows 2 lines . — Neo-coder, Mar 16 '15 at 13:02

score 0 · Answer 1 · answered Apr 24 '22 at 09:49

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

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

1 Answers1

Linked