Insert field with array size in mongo

Question

I have a documents in mongodb, containing some array. Now I need to have a field containing a quantity of items of this array. So I need to update documents adding this field. Simply I thought this will work:

db.myDocument.update({
     "itemsTotal": {
         $exists: false
     },
     "items": {
         $exists: true
     }
 }, {
     $set: {
         itemsTotal: {
             $size: "$items"
         }
     }
 }, {
 multi: true
 })

But it completes with "not okForStorage". Also I tried to make an aggregation, but it throws exception:

"errmsg" : "exception: invalid operator '$size'",
"code" : 15999,
"ok" : 0

What is a best solution and what I do wrong? I'm starting to think about writing java tool for calculation totals and updating documents with it.

Answer 1

You can use the .aggregate() method to $project your documents and return the $size of the items array. After that you will need to loop through your aggregation result using the .forEach loop and $set the itemTotal field for your document using "Bulk" operation for maximum efficiency.

var bulkOp = db.myDocument.initializeUnorderedBulkOp(); 
var count = 0;

db.myDocument.aggregate([
    { "$match": { 
        "itemsTotal": { "$exists": false } ,
        "items": { "$exists": true }
    }}, 
    { "$project": { "itemsTotal": { "$size": "$items" } } }
]).forEach(function(doc) { 
        bulkOp.find({ "_id": doc._id }).updateOne({ 
            "$set": { "itemsTotal": doc.itemsTotal }
        });
        count++;
        if (count % 200 === 0) {
            // Execute per 200 operations and re-init
            bulkOp.execute();
            bulkOp = db.myDocument.initializeUnorderedBulkOp();
        }
})

// Clean up queues
if (count > 0) { 
    bulkOp.execute();
}

Answer 2

You could initialise a Bulk() operations builder to update the document in a loop as follows:

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

db.collection.find("itemsTotal": { "$exists": false },
     "items": {
         $exists: true
     }
).forEach(function(doc) { 
    var items_size = doc.items.length;
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "itemsTotal": items_size }
    });
    count++;
    if (count % 100 == 0) {
        bulk.execute();
        bulk = db.collection.initializeUnorderedBulkOp();
    }
});

if (count % 100 != 0) { bulk.execute(); }

Answer 3

This is much easier starting with MongoDB v3.4, which introduced the $addFields aggregation pipeline operator. We'll also use the $out operator to output the result of the aggregation to the same collection (replacing the existing collection is atomic).

db.myDocuments.aggregate( [
  {
    $addFields: {
      itemsTotal: { $size: "$items" } ,
    },
  },
  {
    $out: "myDocuments"
  }
] )

WARNING: this solution requires that all documents to have the items field. If some documents don't have it, aggregate will fail with

"The argument to $size must be an array, but was of type: missing"

You might think you could add a $match to the aggregation to filter only documents containing items, but that means all documents not containing items will not be output back to the myDocuments collection, so you'll lose those permanently.