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

Takis · Accepted Answer · 2021-12-02 13:58:15Z

2

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

edited Dec 2, 2021 at 13:58

answered Dec 1, 2021 at 23:46

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SpawN Over a year ago

is there any way how to add the sum of every car ive tried but every time im getting just the number of colors. example of what im tring to get : { "car_type": "suv" "red":2, "blue":2, "sum":4 }

Takis Over a year ago

yes its easy to change it i added {"$set": {"sum": {"$sum": "$colors.v"}}} and i think its ok, test it also to be sure it does what you need

SpawN Over a year ago

yes it works just fine thank you

Collectives™ on Stack Overflow

Distinct count of multiple fields values using mongodb aggregation

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related