CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
shulyaev
shulyaev

Reputation: 13

MongoDB (mongoose) - group on categoris and subcategories in document

I have a mongo collection of documents having category, subcategory and item names. Category is an enum ["A", "B", "C"], subCategory and item are Strings.

{ _id: 1, category: "A", subCategory: "a1", item: "item1"}
{ _id: 2, category: "A", subCategory: "a1", item: "item2"}
{ _id: 3, category: "A", subCategory: "a1", item: "item3"}
{ _id: 4, category: "A", subCategory: "a2", item: "item4"}
{ _id: 5, category: "B", subCategory: "b1", item: "item5"}
{ _id: 6, category: "B", subCategory: "b1", item: "item6"}

I am trying to write a query which will return the data in the following format:

{
   A: [
       { subCategory: 'a1', items: ["item1", "item2", "item3"]},
       { subCategory: 'a2', items: ["item4",]}
   ],
   B: [
       { subCategory: 'b1', items: ["item5", "item6"]}
   ]
}

Here is what I have tried so far:

const items = await Items.aggregate([
  {
    $group: {
      _id: { category: "$category", subCategory: "$subCategory" },
      items: { $push: "$item" },
    },
  },
  {
    $group: {
      _id: "$_id.category",
      subCategories: {
        $push: {
          subCategory: "$_id.subCategory",
          items: "$items",
        },
      },
    },
  },
  {
    $project: {
      _id: 0,
      category: "$_id",
      subCategories: 1,
    },
  },
  {
    $addFields: {
      array: [
        {
          k: "$category",
          v: "$subCategories",
        },
      ],
    },
  },
  {
    $replaceRoot: {
      newRoot: { $arrayToObject: "$array" },
    },
  },
]);

And here is the output (close, but not exactly what I need):

[
    {
        A: [
            { subCategory: 'a1', items: ["item1", "item2", "item3"]},
            { subCategory: 'a2', items: ["item4",]}
        ],
    },
    {
        B: [
            { subCategory: 'b1', items: ["item5", "item6"]}
        ]
    },
]

Please advise, how can I achieve the required result

Upvotes: 0

Views: 1621

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Try below aggregation query :

db.collection.aggregate([
    /** Group on `category + subCategory` & push item's to `items` array */
    {
      $group: { _id: { category: "$category", subCategory: "$subCategory" }, items: { $push: "$item" } }
    },
    /** Group on `category` field & push objects({subCategory:...,items:...}) to `v` field */
    {
      $group: { _id: "$_id.category", v: { $push: { subCategory: "$_id.subCategory", items: "$items" } } }
    },
    /** remove `_id` field & add `k` field & project `v` field */
    {
      $project: { _id: 0, k: "$_id", v: 1 }
    },
    /** Replace root doc with arrayToObject convert root doc */
    {
      $replaceRoot: { newRoot: { $arrayToObject: [ [ "$$ROOT" ] ] } }
    },
    /** Group on empty will just group on all docs without a filter & merge all objects  */
    {
      $group: { _id: "", data: { $mergeObjects: "$$ROOT" } }
    },
    { 
        $replaceRoot: { newRoot: "$data" }
    }
  ])

Test : mongoplayground

Ref : aggregation-pipeline

Upvotes: 2

Related Questions