Distinct count of multiple fields values using mongodb aggregation

Question

I'm trying to count distinct values of multiple fields By one MongoDB Aggregation query.

So here's my data:

{
"_id":ObjectID( "617b0dbacda6cbd1a0403f68")
"car_type": "suv",
"color": "red",
"num_doors": 4
},

{
"_id":ObjectID( "617b0dbacda6cbd1a04078df")
    "car_type": " suv ",
    "color": "blue",
    "num_doors": 4

},
{
"_id":ObjectID( "617b0dbacda6cbd1a040ld45")
    "car_type": "wagon",
    "color": "red",
    "num_doors": 4
},
{
"_id":ObjectID( "617b0dbacda6cbd1a0403dcd")
    "car_type": "suv",
    "color": "blue",
    "num_doors": 4
},
{
"_id":ObjectID( "617b0dbacda6cbd1a0403879")
    "car_type": " wagon ",
    "color": "red",
    "num_doors": 4
},
{
"_id":ObjectID( "617b0dbacda6cbd1a0405478")
    "car_type": "wagon",
    "color": "red",
    "num_doors": 4
}

I want a distinct count of each color by car_type:

"car_type": "suv"
"red":2,
"blue":2

iwas able to distinct and cound all colors but i couldnt distinct them by car_type

Answer 1

Query

• group specific first (cartype+color), to count the same colors
• group less specific after (cartype), to get all colors/count for each car_type
• project to fix structure and $arrayToObject to make the colors keys and the the count values

*query assumes that " wagon " was typing mistake(the extra spaces i mean), if your collection has those problems, use $trim to clear the database from those.

*query is updated to include the sum also, from the comment

Test code here

aggregate(
[{"$group": 
    {"_id": {"car_type": "$car_type", "color": "$color"},
      "count": {"$sum": 1}}},
  {"$group": 
    {"_id": "$_id.car_type",
      "colors": {"$push": {"k": "$_id.color", "v": "$count"}}}},
  {"$set": {"sum": {"$sum": "$colors.v"}}},
  {"$project": 
    {"_id": 0,
      "sum": 1,
      "car_type": "$_id",
      "colors": {"$arrayToObject": ["$colors"]}}},
  {"$replaceRoot": {"newRoot": {"$mergeObjects": ["$colors", "$$ROOT"]}}},
  {"$project": {"colors": 0}}])