Need to calculate individual field count from MongoDB embedded collections

Question

I need to count the fields in embedded as well as parent collection in MongoDB. My document consist of multiple embedded collections. I have to retrieve the count of fields instead of actual data from DB.

This is my sample collection,

{
    "_id" : ObjectId("5c58401e354bba286ce4db67"),
    "_class" : "com.model.ProductTemplate",
    "templateName" : "tempnew",
    "printServiceCategories" : [ 
        {
            "_id" : "PSC00001",
            "createdOn" : ISODate("2019-02-04T13:35:52.503Z"),
            "createdBy" : "PRODUCTADMIN",
            "isactive" : true,
            "serviceCategoryDisplayName" : "Finishing",
            "serviceCategoryMappingName" : "Finishing",
            "groupTypes" : [ 
                {
                    "groupTypeId" : "GT00001",
                    "groupTypeDisplayName" : "Binding",
                    "groupTypeMappingName" : "Binding",
                    "printServices" : [ 
                        {
                            "printServiceId" : "PS00003",
                            "printServiceMappingName" : "coil_bind_blue",
                            "printServiceDisplayName" : "Coil Bind Blue",
                            "printServiceImage" : "",
                            "isDefault" : false,
                            "createdBy" : "PRODUCTADMIN"
                        }, 
                        {
                            "printServiceId" : "PS00004",
                            "printServiceDisplayName" : "Coil Bind Black",
                            "isDefault" : true,
                            "createdBy" : "PRODUCTADMIN"
                        }, 
                        {
                            "printServiceId" : "PS00005",
                            "printServiceMappingName" : "comb_bind_black",
                            "printServiceDisplayName" : "Comb Bind Black",
                            "printServiceImage" : "",
                            "isDefault" : false,
                            "createdBy" : "PRODUCTADMIN"
                        }
                    ],
                    "createdBy" : "PRODUCTADMIN"
                }
            ]
        }, 
        {
            "_id" : "PSC00002",
            "createdOn" : ISODate("2019-02-04T13:36:32.794Z"),
            "createdBy" : "PRODUCTADMIN",
            "isactive" : true,
            "serviceCategoryDisplayName" : "Media",
            "serviceCategoryMappingName" : "Media",
            "groupTypes" : [ 
                {
                    "groupTypeId" : "GT00002",
                    "groupTypeDisplayName" : "Paper",
                    "groupTypeMappingName" : "Paper",
                    "printServices" : [ 
                        {
                            "printServiceId" : "PS00006",
                            "printServiceMappingName" : "a3",
                            "printServiceDisplayName" : "A3",
                            "printServiceImage" : "",
                            "isDefault" : false,
                            "createdBy" : "PRODUCTADMIN"
                        }, 
                        {
                            "printServiceId" : "PS00007",
                            "printServiceDisplayName" : "A4",
                            "isDefault" : true,
                            "createdBy" : "PRODUCTADMIN"
                        }
                    ],
                    "createdBy" : "PRODUCTADMIN"
                }
            ]
        }
    ],
    "templateCreatedOn" : ISODate("2019-02-04T13:37:34.025Z"),
    "vendorid" : "5c5838aef57da72804d72ee0",
    "fileoptionstatus" : "withoutFile",
    "isactive" : true,
    "createdBy" : "CLIENTADMIN",
    "additionalServices" : [],
    "file" : {
        "_id" : null
    }
}

Here is above collection of Product Template, I am having three embedded collections namely printServiceCategories, groupTypes,printServices. printServiceCategories consist of array of groupTypes whereas groupTypes consist of array of printServices. While fetching data from database using spring boot application I just need

 1.count of `printServices` in `Grouptype` 

 2.count of `Grouptype` in `printServiceCategories` .

 3.Similarly count of `printServiceCategories`  in `product template`.

Can any one help me to query as per expected output as describe in above?

Jitendra · Accepted Answer · 2019-03-15 05:48:10Z

1

Try something like below:

db.database.aggregate([
    {
       $match: { "_id":ObjectId("5c8a4f4e7c5f002838e61b24") }
    },
    {
        $facet: {
            countOfPrintServices: [
                {
                    $unwind: "$printServiceCategories"
                },
                {
                    $unwind: "$printServiceCategories.groupTypes"
                },
                {
                    $unwind: "$printServiceCategories.groupTypes.printServices"
                },
                {
                    $group: {
                        _id: "$_id",
                        count:{$sum:1}
                    }
                }
            ],
            countOfGrouptypeInPrintServiceCategories : [
                {
                    $unwind: "$printServiceCategories"
                },
                {
                    $unwind: "$printServiceCategories.groupTypes"
                },
                {
                    $group: {
                        _id: "$_id",
                        count:{$sum:1}
                    }
                }
            ],
            countOfPrintServiceCategoriesInProductTemplate: [
                {
                    $unwind: "$printServiceCategories"
                },
                {
                    $group: {
                        _id: "$_id",
                        count:{$sum:1}
                    }
                }
            ]
        }
    }

])

It might be not the optimized solution by you will get your desired result.

{
    "countOfPrintServices" : [
        {
            "_id" : ObjectId("5c58401e354bba286ce4db67"),
            "count" : 5
        }
    ],
    "countOfGrouptypeInPrintServiceCategories" : [
        {
            "_id" : ObjectId("5c58401e354bba286ce4db67"),
            "count" : 2
        }
    ],
    "countOfPrintServiceCategoriesInProductTemplate" : [
        {
            "_id" : ObjectId("5c58401e354bba286ce4db67"),
            "count" : 2
        }
    ]
}

edited Mar 15, 2019 at 5:48

answered Mar 14, 2019 at 6:13

Jitendra

3,1953 gold badges31 silver badges44 bronze badges

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

2 Comments

Ni3 Over a year ago

Thank you jitendra. I have tried this query with my collection named ProductTemplate but It gives me the aggregate of whole collection. But I need to get aggregate of one document from the collection. I have tried the query like, db.productTemplate.findOne({"_id":ObjectId("5c8a4f4e7c5f002838e61b24")}).aggregate([ . But its not working. Can you please help?

Jitendra Over a year ago

No issues. If you want it on the only single (specific) document then you can use $match. I am updating on the post.

Collectives™ on Stack Overflow

Need to calculate individual field count from MongoDB embedded collections

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