Mongo aggregate query for facets navigation

Question

I have a collection products with documents (products) like this (relevant code only):

{ "_id": "prod1", ... "colors": ["red","blue"], "size": ["S", "M"], ...}
{ "_id": "prod2", ... "colors": ["red","green"], "size": ["S", "XL"], ...}
{ "_id": "prod3", ... "colors": ["yellow","blue"], "size": ["XL", "XXL"], ...}

I would like to offer the user only the available selection, as a faceted search, like the name of the option and the number of options available.

colors:

red 2
blue 2
green 1
yellow 1

sizes:

S 2
M 1
XL 2
XXL 1

If I had to do it in SQL, I would probably have to run a query for each facet group.

SELECT colors, count(*) AS number FROM products GROUP BY colors

than the second query

SELECT size, count(*) AS number FROM test GROUP BY sizes

If somebody chose color "red", than I would probably have to add to each query the "WHERE" clause:

WHERE colors = 'red'

Could somebody more experienced than me, help me with this example in MongoDB? Do I have to run two queries or is there something that I am missing and it could be done in one query? I assume I will have a great number of products. Any help appreciated. Especially if there are any tricks to speed it up. I would like to get it right from the beginning, hence asking the question. Thanks.

Answer 1

Here is the solution for your query with MongoDB aggregation. Below is my dummy collection.

    {
        "_id" : ObjectId("584b82055855b8ea7ea29d65"),
        "colors" : [ 
            "Red", 
            "Yellow"
        ],
        "size" : [ 
            "S", 
            "M"
        ]
    }
    {
        "_id" : ObjectId("584b82185855b8ea7ea29d66"),
        "colors" : [ 
            "Red", 
            "Orange"
        ],
        "size" : [ 
            "S", 
            "XL"
        ]
    }
.
.

After running below query.

    db.getCollection('products').aggregate([
    {$unwind : "$colors"},
    {$group : {
        _id : "$colors",
        "sum": {$sum : 1}
    }},
    {
        $project : {
            _id : 0,
            "color":"$_id",
            "count":"$sum"
        }
    }
])

Then the output is :

{
    "color" : "Green",
    "count" : 2
}
{
    "color" : "Orange",
    "count" : 1
}
{
    "color" : "Yellow",
    "count" : 2
}
{
    "color" : "Red",
    "count" : 2
}

If you want to find sum only for Red Color then below query for this

db.getCollection('products').aggregate([
    {$unwind : "$colors"},
    {$match : {"colors":"Red"}},
    {$group : {
        _id : "$colors",
        "sum": {$sum : 1}
    }},
    {
        $project : {
            _id : 0,
            "color":"$_id",
            "count":"$sum"
        }
    }
])

The output of above query is :

{
    "color" : "Red",
    "count" : 2
}

Answer 2

db.entry.aggregate([
{$unwind : "$size" },
{$group: { "_id" : "$size", count : { $sum : 1 } } }
]).pretty()

Hope this helps, you can customize as you wish for the 'count' field and $unwind definition:

Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

https://docs.mongodb.org/manual/reference/operator/aggregation/unwind/