How to retrieve documents with conditioning an array of nested objects?

Question

The structure of the objects stored in mongodb is the following:

obj = {_id: "55c898787c2ab821e23e4661", ingredients: [{name: "ingredient1", value: "70.2"}, {name: "ingredient2", value: "34"}, {name: "ingredient3", value: "15.2"}, ...]}

What I would like to do is retrieve all documents, which value of specific ingredient is greater than arbitrary number.

To be more specific, suppose we want to retrieve all the documents which contain ingredient with name "ingredient1" and its value is greater than 50.

Trying the following I couldn't retrieve desired results:

var collection = db.get('docs');
var queryTest = collection.find({$where: 'this.ingredients.name == "ingredient1" && parseFloat(this.ingredients.value) > 50'}, function(e, docs) {
                                    console.log(docs);
                                });

Does anyone know what is the correct query to condition upon specific array element names and values?

Thanks!

I note here that your "value" entries are in fact strings. So basic lexical comparisons under "500" will work ( string wise ), but it is a better idea to convert these to numeric in storage. — Blakes Seven
– Blakes Seven, Commented Aug 22, 2015 at 11:32

Blakes Seven · Accepted Answer · 2015-08-22 13:02:33Z

You really don't need the JavaScript evaluation of $where here, just use basic query operators with an $elemMatch query for the array. While true that the "value" elements here are in fact strings, this is not really the point ( as I explain at the end of this ). The main point is to get it right the first time:

collection.find(
    {
        "ingredients": {
            "$elemMatch": {
                "name": "ingredient1",
                "value": { "$gt": 50 }
            }
         }
    },
    { "ingredients.$": 1 }
)

The $ in the second part is the postional operator, which projects only the matched element of the array from the query conditions.

This is also considerably faster than the JavaScript evaluation, in both that the evaluation code does not need to be compiled and uses native coded operators, as well as that an "index" can be used on the "name" and even "value" elements of the array to aid in filtering the matches.

If you expect more than one match in the array, then the .aggregate() command is the best option. With modern MongoDB versions this is quite simple:

collection.aggregate([
    { "$match": {
        "ingredients": {
            "$elemMatch": {
                "name": "ingredient1",
                "value": { "$gt": 50 }
            }
         }
    }},
    { "$redact": {
        "$cond": {
            "if": { 
               "$and": [
                   { "$eq": [ { "$ifNull": [ "$name", "ingredient1" ] }, "ingredient1" ] },
                   { "$gt": [ { "$ifNull": [ "$value", 60 ] }, 50 ] }
               ]
            },
            "then": "$$DESCEND",
            "else": "$$PRUNE"
        }
    }}
])

And even simplier in forthcoming releases which introduce the $filter operator:

collection.aggregate([
    { "$match": {
        "ingredients": {
            "$elemMatch": {
                "name": "ingredient1",
                "value": { "$gt": 50 }
            }
         }
    }},
    { "$project": {
        "ingredients": {
            "$filter": {
                "input": "$ingredients",
                "as": "ingredient",
                "cond": {
                    "$and": [
                        { "$eq": [ "$$ingredient.name", "ingredient1" ] },
                        { "$gt": [ "$$ingredient.value", 50 ] }
                    ]
                }
            }
        }
    }}
])

Where in both cases you are effectively "filtering" the array elements that do not match the conditions after the initial document match.

Also, since your "values" are actually "strings" right now, you reaally should change this to be numeric. Here is a basic process:

var bulk = collection.initializeOrderedBulkOp(),
    count = 0;

collection.find().forEach(function(doc) {
    doc.ingredients.forEach(function(ingredient,idx) {
        var update = { "$set": {} };
        update["$set"]["ingredients." + idx + ".value"] = parseFloat(ingredients.value);
        bulk.find({ "_id": doc._id }).updateOne(update);
        count++;

        if ( count % 1000 != 0 ) {
            bulk.execute();
            bulk = collection.initializeOrderedBulkOp();
        }
    })
]);

if ( count % 1000 != 0 )
    bulk.execute();

And that will fix the data so the query forms here work.

This is much better than processing with JavaScript $where which needs to evaluate every document in the collection without the benefit of an index to filter. Where the correct form is:

collection.find(function() {
    return this.ingredients.some(function(ingredient) { 
        return ( 
           ( ingredient.name === "ingredient1" ) && 
           ( parseFloat(ingredient.value) > 50 ) 
        );
    });
})

And that can also not "project" the matched value(s) in the results as the other forms can.

tombarti · Accepted Answer · 2015-08-22 11:16:55Z

0

Try using $elemMatch:

var queryTest = collection.find(
   { ingredients: { $elemMatch: { name: "ingredient1", value: { $gte: 50 } } } }
);

answered Aug 22, 2015 at 11:16

tombarti

4262 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to retrieve documents with conditioning an array of nested objects?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related