MongoDB - Query conundrum - Document refs or subdocument

Question

I've run into a bit of an issue with some data that I'm storing in my MongoDB (Note: I'm using mongoose as an ODM). I have two schemas:

mongoose.model('Buyer',{
  credit: Number,
})

and

mongoose.model('Item',{
  bid: Number,
  location: { type: [Number], index: '2d' }
})

Buyer/Item will have a parent/child association, with a one-to-many relationship. I know that I can set up Items to be embedded subdocs to the Buyer document or I can create two separate documents with object id references to each other.

The problem I am facing is that I need to query Items where it's bid is lower than Buyer's credit but also where location is near a certain geo coordinate.

To satisfy the first criteria, it seems I should embed Items as a subdoc so that I can compare the two numbers. But, in order to compare locations with a geoNear query, it seems it would be better to separate the documents, otherwise, I can't perform geoNear on each subdocument.

Is there any way that I can perform both tasks on this data? If so, how should I structure my data? If not, is there a way that I can perform one query and then a second query on the result from the first query?

Thanks for your help!

score 3 · Accepted Answer · answered May 06 '15 at 23:25

There is another option (besides embedding and normalizing) for storing hierarchies in mongodb, that is storing them as tree structures. In this case you would store Buyers and Items in separate documents but in the same collection. Each Item document would need a field pointing to its Buyer (parent) document, and each Buyer document's parent field would be set to null. The docs I linked to explain several implementations you could choose from.

score 2 · Answer 2 · answered May 12 '15 at 15:07

If your items are stored in two separate collections than the best option will be write your own function and call it using mongoose.connection.db.eval('some code...');. In such case you can execute your advanced logic on the server side.

You can write something like this:

var allNearItems = db.Items.find(
    { location: {
        $near: {
            $geometry: {
              type: "Point" ,
              coordinates: [ <longitude> , <latitude> ]
            },
            $maxDistance: 100
        }
    }
}); 
var res = [];
allNearItems.forEach(function(item){
    var buyer = db.Buyers.find({ id: item.buyerId })[0];
    if (!buyer) continue;
    if (item.bid < buyer.credit) {
        res.push(item.id);
    }
});
return res;

After evaluation (place it in mongoose.connection.db.eval("...") call) you will get the array of item id`s.

Use it with cautions. If your allNearItems array will be too large or you will query it very often you can face the performance problems. MongoDB team actually has deprecated direct js code execution but it is still available on current stable release.

MongoDB - Query conundrum - Document refs or subdocument

2 Answers2