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

Question

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

Without using mapreduce functionality, I'm pretty sure you'd have to simply iterate over the cursor and "manually" count the occurrences within the sub-document. — Lix
– Lix, Commented Aug 12, 2013 at 9:58
The most efficient would be to store the count when you save or modify the document — WiredPrairie
– WiredPrairie, Commented Aug 12, 2013 at 10:38
Unfortunately as of 2.4 there is no ability to do this using the aggregation framework. If you hotlinks had been in an array you could have used $unwind and $sum to calculate the totals but in this case you'll have to use mapReduce or do it in the client — christkv
– christkv, Commented Aug 13, 2013 at 11:45

chridam · Accepted Answer · 2019-01-11 14:58:23Z

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
}

erdimeola · Accepted Answer · 2013-08-12 09:59:57Z

1

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);

answered Aug 12, 2013 at 9:59

erdimeola

8548 silver badges17 bronze badges

3 Comments

Lix Over a year ago

Yep - if not this way, a mapreduce operation would be the other alternative.

karan1001 Over a year ago

Hi Lix And Erdimeola, Thanks so much for your reply. I have thousands of documents in my collection. Thus i need a fast and efficient way to get the count. I feel the javascript way would be a heavy operation. Would mapreduce be efficient... Thanks so much, Karan

karan1001 Over a year ago

Thank you everyone

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related