CODEWITHSUNDEEP
mongodbnosql
Kyle Luke
Kyle Luke

Reputation: 348

MongoDB find if all array elements are in the other bigger array

I have an array of id's of LEGO parts in a LEGO building. 

// building collection
{
   "name": "Gingerbird House",
   "buildingTime": 45,
   "rating": 4.5,
   "elements": [
     {
       "_id": 23,
       "requiredElementAmt": 14
     },
     {
       "_id": 13,
       "requiredElementAmt": 42
     }
   ]
}

and then

//elements collection
 {
 "_id": 23,
 "name": "blue 6 dots brick",
 "availableAmt":20
 }

 {
 "_id": 13,
 "name": "red 8 dots brick",
 "availableAmt":50
 }
 {"_id":254,
 "name": "green 4 dots brick",
 "availableAmt":12
 }

How can I find it's possible to build a building? I.e. database will return the building only if the "elements" array in a building document consists of those elements that I have in a warehouse(elements collection) require less(or equal) amount of certain element.

In SQL(which from I came recently) I would write something likeSELECT * FROM building WHERE id NOT IN(SELECT fk_building FROM building_elemnt_amt WHERE fk_element NOT IN (1, 3)) Thank you in advance!

Upvotes: 0

Views: 107

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

I wont pretend I get how it works in SQL without any comparison, but in mongodb you can do something like that:

db.buildings.find({/* building filter, if any */}).map(function(b){
    var ok = true;
    b.elements.forEach(function(e){
        ok = ok && 1 == db.elements.find({_id:e._id, availableAmt:{$gt:e.requiredElementAmt}}).count();
    })
    return ok ? b : false;
}).filter(function(b){return b});

or 

db.buildings.find({/* building filter, if any */}).map( function(b){
    var condition = [];
    b.elements.forEach(function(e){
        condition.push({_id:e._id, availableAmt:{$gt:e.requiredElementAmt}});
    })
    return db.elements.find({$or:condition}).count() == b.elements.length ? b : false;
}).filter(function(b){return b});

The last one should be a bit quicker, but I did not test. If performance is a key, it must be better to mapReduce it to run subqueries in parallel.

Note: The examples above work with assumption that buildings.elements have no elements with the same id. Otherwise the array of elements needs to be pre-processed before b.elements.forEach to calculate total requiredElementAmt for non-unique ids.

EDIT: How it works:

Select all/some documents from buildings collection with find:

db.buildings.find({/* building filter, if any */})

returns a cursor, which we iterate with map applying the function to each document:

map(function(b){...})

The function itself iterates over elements array for each buildings document b:

b.elements.forEach(function(e){...})

and find number of documents in elements collection for each element e

db.elements.find({_id:e._id, availableAmt:{$gte:e.requiredElementAmt}}).count();

which match a condition: 

elements._id == e._id
and
elements.availableAmt >= e.requiredElementAmt

until first request that return 0.

Since elements._id is unique, this subquery returns either 0 or 1. First 0 in expression ok = ok && 1 == 0 turns ok to false, so rest of the elements array will be iterated without touching the db.

The function returns either current buildings document, or false:

return ok ? b : false

So result of the map function is an array, containing full buildings documents which can be built, or false for ones that lacks at least 1 resource.

Then we filter this array to get rid of false elements, since they hold no useful information:

filter(function(b){return b})

It returns a new array with all elements for which function(b){return b} doesn't return false, i.e. only full buildings documents.

Upvotes: 1

Related Questions