Comparing documents between two MongoDB collections

There is no good way of performing operation like this using MongoDB. If you want BAD way you can use code like this:

db.settings.find().forEach(    function(doc) {        data = db.data.find({            Identifier: doc.Idendtifier,            C: doc.C,            U: doc.U,            $or: [{Value: {$lte: doc.Low}}, {Value: {$gte: doc.High}}]        }).toArray();        // Do what you need    }) 

but don't expect it will perform even remotely as good as any decent RDBMS.

You could rebuild your schema and embed documents from data collection like this:

{    "_id" : ObjectId("527a7f4b07c17a1f8ad009d2"),    "Identifier" : "ABC123",    "C" : "1",    "U" : "V",    "Low" : 116,    "High" : 124,    "ImportLogId" : 1,    "Data" : [        {            "Date" : ISODate("2013-11-06T00:00:00Z"),            "Value" : 128        },        {            "Date" : ISODate("2013-10-09T00:00:00Z"),            "Value" : 99        }    ]}

It may work if number of embedded document is low but to be honest working with arrays of documents is far from being pleasant experience. Not even mention that you can easily hit document size limit with growing size of the Data array.

If this kind of operations is typical for your application I would consider using different solution. As much as I like MongoDB it works well only with certain type of data and access patterns.

Without the concept of JOIN, you must change your approach and denormalize.

In your case, looks like you're doing a data log validation. My advice is looping settings collection and with each of them use the findAndModify operator in order to set a validation flag on data collection records who matches; after that, you could just use the find operator on the data collection, filtering by the new flag.

A feature we've developed called Data Compare & Sync might be able to help here.

It lets you compare two MongoDB collections and see the differences (e.g. spot the same, missing, or different fields).

You can then export these comparison results to a CSV file, and use that to create your new, third collection.

Disclosure: We are the creators of the MongoDB GUI, Studio 3T.