使用 mongodb 聚合的多个字段值的不同计数 [英] Distinct count of multiple fields values using mongodb aggregation

问题描述

我正在尝试通过一个 MongoDB 聚合查询来计算多个字段的不同值.

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

这是我的数据:

{
"_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
}

我想按 car_type 对每种颜色进行不同的计数:

I want a distinct count of each color by car_type:

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

我能够区分和计算所有颜色，但我无法通过 car_type 区分它们

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

推荐答案

查询

• 先分组(车型+颜色)，计算相同的颜色
• 在 (cartype) 之后分组不太具体，以获取每个 car_type
的所有颜色/计数
• 修复结构的项目和 $arrayToObject 制作颜色键和计数值

• 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 假定 "wagon " 输入错误(我的意思是多余的空格)，如果您的收藏有这些问题，请使用 $trim 从这些问题中清除数据库.

*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

在这里测试代码

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

这篇关于使用 mongodb 聚合的多个字段值的不同计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！