Search code examples
mongodbmongoosegroup-byaggregatemern

How to groupby by _id in mongo


For VendorItem collection, I want to have my items grouped by(like SQL) in category but using group in mongo there are no accumulator for _id! Help me Thaks in advance.
param -> vendor's id
Output : [category_id1 : [ it's items ], category_id2: [it's items]]
Model

let vendorItemSchema = mongoose.Schema({ 
    category: { 
        type: mongoose.Schema.Types.ObjectId, 
        required: true, 
        ref:'VendorCategory' 
    },
    vendor : {
        type: mongoose.Schema.Types.ObjectId,
        required: true,
        ref: Vendor
    },
    item: {type: String, required: true}, 
    price: {type: Number, required: true}, 
    inStock: {type: Boolean, required: true}, 
    //order:{} 
});  
vendorItemSchema.index({category: 1}); 

const VendorItem = mongoose.model('VendorItem',vendorItemSchema);

Solution

  • Query

    • probably with group by _id you mean to collect all the document info so you need maybe the $$ROOT
    • the bellow has 3 accumulators, keep only the ones you need
    • sum quantity, collect items(strings) , collect the root documents

    *if this isn't what you need if you can give sample data in json, and the expected output

    aggregate(
    [{"$group":
      {"_id":"$category",
       "count":{"$sum":1},
       "items":{"$push":"$item"},
       "docs":{"$push":"$$ROOT"}}}])