Compare documents between two collections of MongoDB

Question

I have two existing collections and need to fill out a third, based on a comparison between two existing ones. Two collections to be compared have the following scheme:

Settings collection { "Identifier":"ABC123", "C":"1", "U":"V", "Low":116, "High":124, "ImportLogId":1 } Data collection { "Identifier":"ABC123", "C":"1", "U":"V", "Date":"11/6/2013 12AM", "Value":128, "ImportLogId": 1 } 

I am new to MongoDB and NoSQL in general, so it’s hard for me to have time to do this. SQL would look something like this.

 SELECT s.Identifier, r.ReadValue, rU, rC, r.Date FROM Settings s JOIN Reads r ON s.Identifier = r.Identifier AND sC = rC AND sU = rU WHERE (r.Value <= s.Low OR r.Value >= s.High) 

In this case, using the sample data, I would like to return the record, because the value from the data collection is greater than the high value from the collection of settings. Is this possible with Mongo queries or map shrinking, or is this a bad collection structure (i.e. maybe it should all be in one collection)?

A few additional notes: A collection of settings should have only 1 entry for "Identifier". Data collection will have many entries on the "Identifier". This process can potentially scan hundreds of thousands of documents at a time, so looking at resources is somewhat important.

Answer 1

There is no good way to perform an operation using MongoDB. If you want a BAD , you can use this code:

 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 do not expect it to work even remotely, like any decent DBMS.

You can rebuild your schema and insert documents from the data collection as follows:

 { "_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 } ] } 

This may work if the number of embedded documents is small, but working honestly with document arrays is far from a pleasant experience. Don't even mention that you can easily achieve document size limits as the size of the data array grows.

If this type of operation is typical of your application, I would consider using a different solution. As far as I like MongoDB, it only works well with certain data types and access patterns.

Answer 2

Without the JOIN concept, you have to change your approach and denormalize.

In your case, it looks like you are doing a data log check. My advice is a set of loop settings, and each of them uses the findAndModify statement to set the check flag on data records that match; after that, you can simply use the find operator to collect data by filtering the new flag.