MongoDB select distinct and count

Question

I have a product collection which looks like 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 would like to select and count the distinct categories and the number of products that have the category (Note that a product can have more than one category). Something like that:

[
  {
    "category": "kitchen",
    "numberOfProducts": 2
  },
  {
    "category": "bedroom",
    "numberOfProducts": 1
  },
  {
    "category": "livingroom",
    "numberOfProducts": 1
  }
]

And it would be better if I could get the same result for each different facet type, something like 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 am doing tests with distinct, aggregate and mapReduce. But can't achieve the results needed. Can anybody tell me the good way?

UPDATE:

With aggregate, this give me the different facet categories that a product have, but not the values nor the count of different values:

db.products.aggregate([
    {$match:{'content.facets.type':'category'}}, 
    {$group:{ _id: '$content.facets.type'} }  
]).pretty();

Answer 1

The following aggregation pipeline will give you the desired result. In the first pipeline step, you need to do an $unwind operation on the facets array so that it's deconstructed to output a document for each element. After the $unwind stage is the first of the $group operations which groups the documents from the previous stream by category and type and calculates the number of products in each group using $sum. The next $group operation in the next pipeline stage then creates the array that holds the aggregated values by using $addToSet operator. The final pipeline stage is the $project operation which then transforms the document in the stream by 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
}