skip to Main Content

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,
}
}

2

Answers


  1. Chosen as BEST ANSWER

    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


  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search