MongoDB: search for minimum, maximum in a nested object with the name of a dynamic field

Question

I have a query to get the minimum query and maximum query below the sample dataset. In my case, the field names are dynamic, for example 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 do not understand how I can do this in Mongo, where the field names are dynamic, which means that in the future there may be other products that are also created as product_4 and product_5 for the same id .

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

Answer 1

You must 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 can ask the usual 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 the structure of your documents by updating them. You will need to loop through each document using the .forEach method, then $unset all the field name starting with product_ . From there, you will need to add new products fields, which are an array of products using the $set update operator. In doing so, you should use "bulk" 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 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, your aggregation request is executed using the .aggregate() method:

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

What returns:

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

From version 3.2 you can use $max and $min in $project , which is a much better way to do this because there is no need for $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" } } } }} ]) 

What gives:

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