CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
karan1001
karan1001

Reputation: 121

Mongo: Query to get count of objects in a nested field

I needed some help to create a count query on nested objects in a field, across all documents. Each document json has a many fields. One particular field called "hotlinks" comprises of many internal dynamic object fields.

Doc1:
{
  hotlinks : { 112222:{....} , 333333: {.....} , 545555: {.....}      }
}

Doc2:
{
  hotlinks : { 67756:{....} , 756767: {.....} , 1111111: {.....}      }
}

Each document has a hotlinks fields. The hotlinks field comprises of varied inner hotlink objects. Each key is a java unique id and has objects that contain data (inner fields).

I needed a way to get the count of all the inner nested objects of the field – ‘hotlinks’. For example the summation of inner objects of hotlinks in doc1 and doc2 would be 6.

Is there any way to do this via a single query to get the count across all documents.

Thanks a lot, Karan

Upvotes: 1

Views: 6332

Answers (2)

chridam
chridam

Reputation: 103325

Quite possible if using MongoDB 3.6 and newer though the aggregation framework. Use the $objectToArray operator within an aggregation pipeline to convert the document to an array. The return array contains an element for each field/value pair in the original document. Each element in the return array is a document that contains two fields k and v.

On getting the array, you can then leverage the use of the $size operator which returns the number of elements in the given array thus giving you the count per document.

Getting the count across all the documents requires a $group pipeline where you specify the _id key of null or a constant value which gives calculates accumulated values for all the input documents as a whole.

All this can be done in a single pipeline by nesting the expressions as follows:

db.collection.aggregate([
    { "$group": {
        "_id": null,
        "count": {
            "$sum": { 
                "$size": { "$objectToArray": "$hotlinks" }
            }
        }
    } }     
])

Example Output

{
    "_id" : null,
    "count" : 6
}

Upvotes: 3

erdimeola
erdimeola

Reputation: 854

this may not be the best approach, but you can define a javascript variable and sum up the counts. i.e;

var hotlinkTotal=0;
db.collection.find().forEach(function(x){hotlinkTotal+=x.hotlinks.length;});
print(hotlinkTotal);

Upvotes: 1

Related Questions