$sum values within array in mongodb

Question

We have a Mongo collection that has this form:

[
    {
        "_id" : "34210db0-6g91-83e8-ae8c-659f064f503f",
        "dateReceived" : ISODate("2018-01-01T00:00:00.000Z"),
        "group" : null,
        "clientShortName" : "My Client Name",
        "sourceShortName" : "Datasource Name",
        "files" : [ 
            {
                "_id" : "807061f0-2d77-87e8-8610-9ff3cbc9c774"
                "status" : 1,
                "fileName" : "filename1.csv",
                "numRows" : 15,

            }, 
            {
                "_id" : "587036f0-2n65-55e8-8610-3ee3cbc9c814"
                "status" : 8,
                "fileName" : "filename2.csv",
                "numRows" : 30,
            }
        ]
    }
]

We have a Mongo query using "find" in conjunction with "map" in order to produce a transformed output. The find/map command looks like this:

db.getCollection('batches')
.find({_id: "34210db0-6g91-83e8-ae8c-659f064f503f"}, {"__v": false, "files.diffHistory": false})
.map( doc =>
    {
        doc.id = doc._id;
        doc.clientName = doc.clientShortName;
        doc.dataSourceName = doc.sourceShortName;
        delete doc._id;
        delete doc.clientShortName;
        delete doc.sourceShortName;

        doc.numFiles = NumberInt(doc.files.length);

        doc.files = doc.files.map( file =>
            {
                file.id = file._id;
                delete file._id;
                delete file.__v;
                delete file.edits;

                return file;
            }
        );

        // broken....how should this be formatted?    
        doc.totalNumRows = {$sum: doc.files.numRows};

        return doc;
    }
)

This find/map works and produces the expected output EXCEPT for the "totalNumRows" summation. What we are trying to accomplish is to add up all of the "files.numRows" fields so that we get a single summarised entry at the top level of the returned data set. Ie we'd see a result set that looks like:

[
        {
            "id" : "34210db0-6g91-83e8-ae8c-659f064f503f",
            "dateReceived" : ISODate("2018-01-01T00:00:00.000Z"),
            "group" : null,
            "clientName" : "My Client Name",
            "dataSourceName" : "Datasource Name",
            "files" : [ 
                {
                    "id" : "807061f0-2d77-87e8-8610-9ff3cbc9c774"
                    "status" : 1,
                    "fileName" : "filename1.csv",
                    "numRows" : 15,

                }, 
                {
                    "id" : "587036f0-2n65-55e8-8610-3ee3cbc9c814"
                    "status" : 8,
                    "fileName" : "filename2.csv",
                    "numRows" : 30,
                }
            ],

            "totalNumRows": 45

        }
    ]

Everything tried so far produces a broken query. Does anyone know the correct command/format for summing up the "numRows" field of our "file" sub-doc?

Ashh · Accepted Answer · 2018-06-14 20:21:20Z

1

Well you can do something better and faster approach then that... Try $project to rename your actual field name and then $sum to get total count of numRows

db.collection.aggregate([
  { "$match": { _id: "34210db0-6g91-83e8-ae8c-659f064f503f" }},
  { "$project": {
    "totalNumRows": {
      "$sum": "$files.numRows"
    },
    "clientName": "$clientShortName",
    "dataSourceName": "$sourceShortName",
    "files": "$files"
  }}
])

It will give following output

[
  {
    "_id": 1111,
    "clientName": "My Client Name",
    "dataSourceName": "Datasource Name",
    "files": [
      {
        "fileName": "filename1.csv",
        "id": 2222,
        "numRows": 15,
        "status": 1
      },
      {
        "fileName": "filename2.csv",
        "id": 3333,
        "numRows": 30,
        "status": 8
      }
    ],
    "totalNumRows": 45
  }
]

answered Jun 14, 2018 at 20:21

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gatmando Over a year ago

Brilliant! Works perfectly and does appear to be faster under initial load testing. Thanks a million!

Ashh Over a year ago

@Gatmando Just a tip... Don't do anything like mapping or looping after query because it is always a time taking task... Always use mongodb aggregation operators to do this types things... They are lightning fast...

Collectives™ on Stack Overflow

$sum values within array in mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related