Home > Blockchain >  How to groupby by _id in mongo
How to groupby by _id in mongo

Time:03-03

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);

CodePudding user response:

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"}}}])

CodePudding user response:

It's the equivalent of the following SQL instruction: SELECT COUNT() FROM Table GROUP BY your_field HAVING COUNT() > N

**query = db.collection.aggregate([

    { 
      "$group": { "_id": "$your_field", #GROUP BY your_field
                "count": {"$sum":1} }   #COUNT(*)
    },
    
    { "$match": { "count": { "$gt": N } } } #HAVING COUNT(*) > N
])**
  • Related