How to find the equivalent of broken "foreign key" relationships?

Question

I have two collections that we will call a one-to-one relationship if it was a relational database. I do not know why one is not nested in the other, but the fact is that for each document in collection "A" the document in collection "B" is meant and vice versa.

Of course, in the absence of foreign key restrictions and in the presence of errors, sometimes there is a document in "A" that does not have a corresponding document in "B" (or vice versa).

I am new to MongoDB, and I am having trouble creating a query or script that will find me all documents in "A" that don't have a linked document in "B" (and vice versa). I think I could use some kind of loop, but I don’t know how it will work - I just started using simple queries on the RoboMongo command line.

Can anyone get me using scripts? I saw " Checking the integrity of the link (foreign key) in MongoDB ", but this does not help me. The error caused referential integrity to break, and I need scripts to help me track the error. I also cannot reverse engineer the database to use attachments (although I expect that I will ask why one document is not attached to another).

I also saw " How to find items in collections that are not in another collection with MongoDB, " but it has no answers.

Pseudo code for bad technology

var misMatches = [];
var collectionB = db.getCollection('B');
var allOfA = db.getCollection('A').find();
while (allOfA.hasNext()) {
    var nextA = allOfA.next();
    if (!collectionB.find(nextA._id)) {
        misMatches.push(nextA._id);
    }
}

Answer 1

I don’t know if this is good, but ...

... given this example:

> db.a.insert([{a:1},{a:2},{a:10}       ])
> db.b.insert([      {b:2},{b:10},{b:20}])
//             ^^^^^              ^^^^^^
//                inconsistent 1-to-1 relationship

You can use map-reduce to collect the key set in aand merge it using the key set from b:

mapA=function() {
  emit(this.a, {col: ["a"]})
}

mapB=function() {
  emit(this.b, {col: ["b"]})
}

reduce=function(key, values) {
  // merge both `col` arrays; sort the result
  return {col: values.reduce(
                 function(a,b) { return a.col.concat(b.col) }
                            ).sort()}
}

Production:

> db.a.mapReduce(mapA, reduce, {out:{replace:"result"}})
> db.b.mapReduce(mapB, reduce, {out:{reduce:"result"}})
> db.result.find()
{ "_id" : 1, "value" : { "col" : [ "a" ] } }
{ "_id" : 2, "value" : { "col" : [ "a", "b" ] } }
{ "_id" : 10, "value" : { "col" : [ "a", "b" ] } }
{ "_id" : 20, "value" : { "col" : [ "b" ] } }

Then it’s pretty easy to find all the id that were not found in the collection aand b. In addition, you should be able to detect duplicate keys in a particular collection:

> db.result.find({"value.col": { $ne: [ "a", "b" ]}})
{ "_id" : 1, "value" : { "col" : [ "a" ] } }
{ "_id" : 20, "value" : { "col" : [ "b" ] }