MongoDB: Search minimum, maximum in nested object with dynamic field name

Question

I have a query to get the minimum query and maximum query on below sample data set. In my case fields names are dynamic, like below product_1, product_2...

{
    "_id" : NumberLong(540),
    "product_1" : {
            "orderCancelled" : 0,
            "orderDelivered" : 6
    },
    "product_2" : {
            "orderCancelled" : 3,
            "orderDelivered" : 16
    },
    "product_3" : {
            "orderCancelled" : 5,
            "orderDelivered" : 11
    }
}

I am not getting an idea how can i do this in Mongo where the field names are dynamic, means in future there may be other products also get created as product_4 and product_5 for the same id.

I need a query which gives me minimum value for orderDelivered and maximum value for orderCancelled, as for example in above document result will be orderDelivered:16 & orderCancelled:0 .
Thanks for any idea.

Answer 1

You should restructure your document so that all product documents are in an array:

{
    "_id": NumberLong(540),
    products: [
        {
            "name": "product_1",
            "orderCancelled": 0,
            "orderDelivered": 6
        },
        {
            "name": "product_2",
            "orderCancelled": 3,
            "orderDelivered": 16
        },
        {
            "name": "product_3",
            "orderCancelled": 5,
            "orderDelivered": 11
        }
    ]
}

Then you'll be able to issue normal max/min queries like this:

db.test.aggregate([
    {   
        $match: { "_id" : NumberLong(540) }
    },
    {
        $unwind: "$products"
    },
    {
        $group: {
            _id: "$_id",
            minDelivered: { $min: "$products.orderDelivered" },
            maxCancelled: { $max: "$products.orderCancelled" }
        }
    }
])

Answer 2

You need to change your documents structure by updating them. You will need loop through each document using the .forEach method then $unset all field's name that startswith product_. From there you will need to add new fields products which is array of products using the $set update operator. That being said you should use "bulk" operations to update your documents for maximum efficiency

var bulkOp = db.collection.initializeOrderedBulkOp();
var count = 0;
db.collection.find().forEach(function(doc) {
    var allproducts = [];
    for(var key in doc) {   
        if(Object.prototype.hasOwnProperty.call(doc, key) && /^product_\d+/.test(key)) { 
            var product = {}; 
            product["name"] = key;    
            product["orderCancelled"] = doc[key]["orderCancelled"]; 
            product["orderDelivered"] = doc[key]["orderDelivered"];
            allproducts.push(product);
            var unsetField = {}; 
            unsetField[key] = ""; 
            bulkOp.find({"_id": doc._id}).update({ "$unset": unsetField });
        }; 
        count++; 
    };
    bulkOp.find({"_id": doc._id}).update({ 
        "$set": { "products": allproducts } 
    });
    count++; 
    if(count % 500 === 0) { 
        // Execute per 500 operations and re-init
        bulkOp.execute(); 
        bulkOp = db.collection.initializeOrderedBulkOp();
    }
})

// clean up queues

if(count > 0) {
    bulkOp.execute(); 
}

Your documents will now look like this:

{
        "_id" : NumberLong(542),
        "products" : [
                {
                        "name" : "product_1",
                        "orderCancelled" : 0,
                        "orderDelivered" : 6
                },
                {
                        "name" : "product_2",
                        "orderCancelled" : 3,
                        "orderDelivered" : 16
                },
                {
                        "name" : "product_3",
                        "orderCancelled" : 5,
                        "orderDelivered" : 11
                }
        ]
}

Then comes your aggregation query using the .aggregate() method:

db.collection.aggregate([ 
    { "$match": { "_id": 542 } }, 
    { "$unwind": "$products" }, 
    { "$group": { 
        "_id": "$_id", 
        "maxOrderCancelled": { "$max": "$products.orderCancelled"}, 
        "minOrderDelivvered": { "$min": "$products.orderDelivered"}
    }}
])

Which returns:

{ "_id" : NumberLong(542), "maxOrderCancelled" : 5, "minOrderDelivvered" : 6 }

From version 3.2 you can use the $max and $min in your $project stage which is a much more better way to do this because there no need to $unwind your array first.

db.collection.aggregate([ 
    { "$match": { "_id": 542 } }, 
    { "$project": { 
        "maxOrderCancelled": { 
            "$max": { 
                "$map": { 
                    "input": "$products", 
                    "as": "order", 
                    "in": "$$orc.orderCancelled" 
                } 
            } 
        }, 
        "minOrderDelivered": { 
            "$min": { 
                "$map": { 
                    "input": "$products", 
                    "as": "orc", 
                    "in": "$$orc.orderDelivered" 
                } 
             } 
        } 
    }} 
])

Which yields:

{ "_id" : NumberLong(542), "maxOrderCancelled" : 5, "minOrderDelivered" : 6 }