MongoDB select distinct and count -
i have product collection looks that:
products = [ { "ref": "1", "facets": [ { "type":"category", "val":"kitchen" }, { "type":"category", "val":"bedroom" }, { "type":"material", "val":"wood" } ] }, { "ref": "2", "facets": [ { "type":"category", "val":"kitchen" }, { "type":"category", "val":"livingroom" }, { "type":"material", "val":"plastic" } ] } ] i select , count distinct categories , number of products have category (note product can have more 1 category). that:
[ { "category": "kitchen", "numberofproducts": 2 }, { "category": "bedroom", "numberofproducts": 1 }, { "category": "livingroom", "numberofproducts": 1 } ] and better if same result each different facet type, that:
[ { "facettype": "category", "distinctvalues": [ { "val": "kitchen", "numberofproducts": 2 }, { "val": "livingroom", "numberofproducts": 1 }, { "val": "bedroom", "numberofproducts": 1 } ] }, { "facettype": "material", "distinctvalues": [ { "val": "wood", "numberofproducts": 1 }, { "val": "plastic", "numberofproducts": 1 } ] } ] i doing tests distinct, aggregate , mapreduce. can't achieve results needed. can tell me way?
update:
with aggregate, give me different facet categories product have, not values nor count of different values:
db.products.aggregate([ {$match:{'content.facets.type':'category'}}, {$group:{ _id: '$content.facets.type'} } ]).pretty();
the following aggregation pipeline give desired result. in first pipeline step, need $unwind operation on facets array it's deconstructed output document each element. after $unwind stage first of $group operations groups documents previous stream category , type , calculates number of products in each group using $sum. next $group operation in next pipeline stage creates array holds aggregated values using $addtoset operator. final pipeline stage $project operation transforms document in stream modifying existing fields:
var pipeline = [ { "$unwind": "$facets" }, { "$group": { "_id": { "facettype": "$facets.type", "value": "$facets.val" }, "count": { "$sum": 1 } } }, { "$group": { "_id": "$_id.facettype", "distinctvalues": { "$addtoset": { "val": "$_id.value", "numberofproducts": "$count" } } } }, { "$project": { "_id": 0, "facettype": "$_id", "distinctvalues": 1 } } ]; db.product.aggregate(pipeline); output
/* 0 */ { "result" : [ { "distinctvalues" : [ { "val" : "kitchen", "numberofproducts" : 2 }, { "val" : "bedroom", "numberofproducts" : 1 }, { "val" : "livingroom", "numberofproducts" : 1 } ], "facettype" : "category" }, { "distinctvalues" : [ { "val" : "wood", "numberofproducts" : 1 }, { "val" : "plastic", "numberofproducts" : 1 } ], "facettype" : "material" } ], "ok" : 1 }