I am fetching for category and subcategory i am using the following pipeline Category:

{
                "$match": {
                    "category": {
                         "$in": ["list of my categories"]
                    }
                }
            },
            {
                "$group": {
                    "category": "$category",
                    "count": {
                        "$sum": 1
                    }
                }
            },
            

This gives me:

{category: category name,
count: totalcount}

Subcategory pipeline

            {
                "$match": {
                    "category": {
                        "$in": ["list of my categories"]
                    }
                }
            },
            {
                "$group": {
                    "_id": { subCategory: "$subCategory", category: "$category" },
                    "count": {
                        "$sum": 1
                    }
                }
            },
            {
                "$group": {
                    "_id": "$_id.category",
                    "counts": {
                        "$push": {
                            "k": "$_id.subCategory",
                            "v": "$count"
                        },
                    },
                    "count":{
                        $sum: "$counts"
                    }
                }
            },
            {
                "$project": {
                    "counts": { "$arrayToObject": "$counts" },
                }
            },

This gives me

category: name {

subcategory1 : total count,
...
}

How do I join the two to get a single call to return something like

{category: categoryname,
count: totalcountforcategory,
subcategories: {
subcategory: totalcount,
subcategory2:totalcount}

Update

Here is my sample JSON

{
    "category": "Category one",
    "name": "Sample name",
    "subCategory": "subCategory one",
},
{
    "category": "Category one",
    "name": "Sample name",
    "subCategory": "subCategory two",
},
{
    "category": "Category two",
    "name": "Sample name",
    "subCategory": "subCategory one",
},
{
    "category": "Category one",
    "name": "Sample name",
    "subCategory": "subCategory two",
}

Expected OutPut

{
"Category one": 3,
subCategories: {
"subCategoryone": 2,
"subCategorytwo":3,
}
}

{
"Category two": 5,
subCategories: {
"subCategoryone": 2,
"subCategorytwo":3,
}
}

CodePudding user response：

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        cat: "$category",
        sub: "$subCategory"
      },
      "count": {
        "$sum": 1
      },
      "subCategory": {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.cat",
      "counts": {
        "$push": {
          "k": "$_id.sub",
          "v": "$count"
        },
        
      },
      "count": {
        $sum: "$counts"
      }
    }
  },
  {
    "$project": {
      "counts": {
        "$arrayToObject": "$counts"
      },
      
    }
  },
])

Playground

CodePudding user response：

I used $facet to join pipelines i don't know if it is best practice or not

const pipeline = [
        {
            "$facet": {
                "GroupAll":
                    [{ $project: { name: 1, category: 1,subCategory:1,votes:1 } }]
                ,
                "GroupTotal": [
                    {
                        "$match": {
                            "nominationYear": {
                                "$eq": "2022"
                            }
                        }
                    },
                    {
                        $count: "total"
                    }
                ],
                "GroupCategories": [
                    {
                        "$match": {
                            "category": {
                                "$in": ["Categories"]
                            }
                        }
                    },
                    {
                        "$group": {
                            "_id": { category: "$category" },
                            "count": {
                                "$sum": 1
                            }
                        }
                    },
                ],
                "GroupSubCategories": [
                    {
                        "$match": {
                            "category": {
                                "$in": ["Categories"]
                            }
                        }
                    },
                    {
                        "$group": {
                            "_id": { subCategory: "$subCategory", category: "$category" },
                            "count": {
                                "$sum": 1
                            }
                        }
                    },

                    {
                        "$group": {
                            "_id": "$_id.category",
                            "counts": {
                                "$push": {
                                    "k": "$_id.subCategory",
                                    "v": "$count"
                                },
                            },
                            "count": {
                                $sum: "$counts"
                            }
                        }
                    },

                    {
                        "$project": {
                            "counts": { "$arrayToObject": "$counts" },
                        }
                    },
                ]
            }
        }

    ]

And here is my output

    {
  "nominations": [
    {
      "GroupAll": [""],
      "GroupTotal": [""],
      "GroupCategories": [""],
      "GroupSubCategories":[""]
    }
  ],
  "total": ""
}

I achieved it using $facet to join multiple pipelines