MongoDB按多个字段分组

Question

我在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"
}
]

我需要得到：

1. 每种体型的颜色数量
2. 拥有两个汽车品牌的公司

我试过第一个这样的：

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

但我得到了所有的颜色，包括那些重复两次的颜色。我需要得到一些不同的颜色。

我想不出任何关于第二个的建议。

谢谢。

Answer 1

每种车身类型的颜色数量

你可以这样做：

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

注意，我包含了所有颜色的数组以及colors_array属性。如果您只想要一些唯一的颜色，而不是颜色阵列，只需将"colors_array": 1更改为"colors_array": -1即可。

工作示例

拥有两个汽车品牌的公司

你可以这样做：

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

工作示例