I have a MongoDB schema that looks like this

const ProductModel = new Schema({
subcategory: {
    type : mongoose.Schema.Types.ObjectId,
    ref : "Subcategory",
},
product_name: {
    type: String
},
description: {
    type: String
},
price: {
    type: Number
},
});

And a subcategory schema:

const SubcategoryModel = new Schema({
    subcategoryName: {
        type: String,
    }
});

The input query before aggregation looks like this:

[
    {
        "_id": "111",
        "subcategory": {
            "_id": "456",
            "categoryName": "Sneakers",
        },
        "product_name": "Modern sneaker",
        "description": "Stylish",
        "price": 4400
    },
    {
        "_id": "222",
        "subcategory": {
            "_id": "456",
            "categoryName": "Sneakers",
        },
        "product_name": "Blue shoes",
        "description": "Vived colors",
        "price": 7500
    },
    {
        "_id": "333",
        "subcategory": {
            "_id": "123",
            "categoryName": "Jackets",
            "__v": 0
        },
        "product_name": "Modern jacket",
        "description": "Stylish",
        "price": 4400
    },
    }
]

The final result of the query should look like this:

{
   "Sneakers":[
      {
         "product_name":"Modern sneaker",
         "description":"Stylish",
         "price":"4400"
      },
      {
         "product_name":"Blue shoes",
         "description":"Vived colors",
         "price":"7500"
      },
      "Jackets":{
         "...."
      }
   ]
}

Subcategory before aggregation:

"subcategories": [
  {
    "_id": "123",
    "categoryName": "Jackets",
    
  },
  {
    "_id": "456",
    "categoryName": "Sneakers",
    
  }
]

I'm trying to populate the subcategory, And then group the products by their subcategoryName field.

CodePudding user response：

You can use this aggregation query:

• First $lookup to do the join between Product and Subcategory creating the array subcategories.
• Then deconstructs the array using $unwind.
• $group by the name of subproduct adding the entire object using $$ROOT.
• The passes the fields you want using $project.
• And replaceRoot to get key value into arrays as Sneakers and Jackets.

db.Product.aggregate([
  {
    "$lookup": {
      "from": "Subcategory",
      "localField": "subcategory.categoryName",
      "foreignField": "categoryName",
      "as": "subcategories"
    }
  },
  {
    "$unwind": "$subcategories"
  },
  {
    "$group": {
      "_id": "$subcategories.categoryName",
      "data": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$project": {
      "data": {
        "product_name": 1,
        "description": 1,
        "price": 1
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": [
          [
            {
              "k": "$_id",
              "v": "$data"
            }
          ]
        ]
      }
    }
  }
])

Example here

With your provided data, result is:

[
  {
    "Sneakers": [
      {
        "description": "Stylish",
        "price": 4400,
        "product_name": "Modern sneaker"
      },
      {
        "description": "Vived colors",
        "price": 7500,
        "product_name": "Blue shoes"
      }
    ]
  },
  {
    "Jackets": [
      {
        "description": "Stylish",
        "price": 4400,
        "product_name": "Modern jacket"
      }
    ]
  }
]