MongoDB group by multiple fields-CodePudding

I have a collection with documents in MongoDB:

    [
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2c7"),
        "Car_brand":"BMW X7",
        "Plate_number":"8OP66",
        "Model_year":"2018",
        "Company":"BMW",
        "Purchase_year":"2019",
        "Body_color":"red",
        "Mileage":1000,
        "Price":35000,
        "Body_type":"crossover"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2c8"),
        "Car_brand":"Tesla Model X",
        "Plate_number":"5XR56",
        "Model_year":"2015",
        "Company":"Tesla Motors",
        "Purchase_year":"2019",
        "Body_color":"white",
        "Mileage":800,
        "Price":25000,
        "Body_type":"SUV"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2c9"),
        "Car_brand":"Tesla Cybertruck",
        "Plate_number":"2ED45",
        "Model_year":"2021",
        "Company":"Tesla Motors",
        "Purchase_year":"2021",
        "Body_color":"gray",
        "Mileage":0,
        "Price":50000,
        "Body_type":"pickup"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2ca"),
        "Car_brand":"Lamborghini Aventador",
        "Plate_number":"2MN50",
        "Model_year":"2011",
        "Company":"Lamborghini",
        "Purchase_year":"2017",
        "Body_color":"orange",
        "Mileage":700,
        "Price":45000,
        "Body_type":"supercar"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2cb"),
        "Car_brand":"BMW X7",
        "Plate_number":"3QW14",
        "Model_year":"2018",
        "Company":"BMW",
        "Purchase_year":"2020",
        "Body_color":"black",
        "Mileage":4500,
        "Price":14000,
        "Body_type":"crossover"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2cc"),
        "Car_brand":"Mercedes-Benz G-Class",
        "Plate_number":"9KI24",
        "Model_year":"2017",
        "Company":"Mercedes-Benz",
        "Purchase_year":"2017",
        "Body_color":"black",
        "Mileage":6000,
        "Price":13000,
        "Body_type":"SUV"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2cd"),
        "Car_brand":"BMW X6",
        "Plate_number":"2GH47",
        "Model_year":"2008",
        "Company":"BMW",
        "Purchase_year":"2016",
        "Body_color":"white",
        "Mileage":4500,
        "Price":14500,
        "Body_type":"SUV"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2ce"),
        "Car_brand":"Chevrolet Camaro",
        "Plate_number":"7BV58",
        "Model_year":"2015",
        "Company":"Chevrolet",
        "Purchase_year":"2020",
        "Body_color":"orange",
        "Mileage":4000,
        "Price":43000,
        "Body_type":"cabriolet"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2cf"),
        "Car_brand":"Ford Mustang",
        "Plate_number":"4AM23",
        "Model_year":"2016",
        "Company":"Ford Motor Company",
        "Purchase_year":"2019",
        "Body_color":"purple",
        "Mileage":2000,
        "Price":30000,
        "Body_type":"cabriolet"
    },
    {
        "_id" : ObjectId("61ba65af74cf385ee93ad2d0"),
        "Car_brand":"Dodge Challenger",
        "Plate_number":"6DL73",
        "Model_year":"2020",
        "Company":"Dodge (Chrysler Corporation)",
        "Purchase_year":"2020",
        "Body_color":"red",
        "Mileage":0,
        "Price":40000,
        "Body_type":"muscle car"
    }
    ]

I need to get:

Number of colors for each body type.
The company with two car brands.

I've tried the first one like this:

db.Vehicles.aggregate([{$group:{"_id":"$Body_type","Количество цветов":{$sum: 1}}}]);

But I get all the colors including those which repeat twice. And I need to get a number of distinct colors.

And I can't think of any suggestions for the second one.

Thanks.

CodePudding user response：

Number of colors for each body type

You can do it like this:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$Body_type",
      "colors_array": {
        "$addToSet": "$Body_color"
      }
    }
  },
  {
    "$project": {
      "colors_number": {
        "$size": "$colors_array"
      },
      "colors_array": 1
    }
  }
])

Note that I included the array of all the colors as well - colors_array property. If you want only number of unique colors, and not colors array in addition, you just change "colors_array": 1 to "colors_array": -1.

Working example

The company with two car brands.

You can do it like this:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$Company",
      "brands_array": {
        "$addToSet": "$Car_brand"
      }
    }
  },
  {
    "$set": {
      "brands_number": {
        "$size": "$brands_array"
      }
    }
  },
  {
    "$match": {
      "brands_number": 2
    }
  }
])

Working example