时间戳

首页 > 解决方案 > mongodb: match, group by multiple fields, project and count

mongodb - mongodb: match, group by multiple fields, project and count

问题描述

So I'm learning mongodb and I got a collection of writers to train. Here I'm trying to count works by sorting them by country and gender of the author. This is what I accoplished so far:

db.writers.aggregate([
    { "$match": { "gender": {"$ne": male}}},
    { "$group": {
        "_id": {
            "country_id": "$country_id",
            "type": "$type"
        },
     }},
    { "$group": {
        "_id": "$_id.country_id",
        "literary_work": {
            "$push": { 
                "type": "$_id.type",
                "count": { "$sum": "$type" }
            }
        },
        "total": { "$sum": "$type" }
    }},
    { "$sort": { "country_id": 1 } },
    { "$project": {
        "literary_work": { "$slice": [ "$literary_work", 3 ] },
        "total": { "$sum": "$type" }
    }} 
])

Sadly, the output that I get is not the one I'm expecting:


 "_id" : GREAT BRITAIN,
    "literary_work" : [ 
        {
            "type" : "POEM",
            "count" : 0
        }, 
        {
            "type" : "NOVEL",
            "count" : 0
        }, 
        {
            "type" : "SHORT STORY",
            "count" : 0
        }
    ],
    "total" : 0

Could anyone tell me where do I insert the count stage or what is my mistake?)

upd:

Data sample:


{   
    "_id" : ObjectId("5f115c5d5f62f9f482cd7a49"),
    "author" : George Sand,
    "gender" : female,
    "country_id" : FRANCE,
    "title": "Consuelo",
    "type" : "NOVEL",
    
}

Expected result (NB! this is a result for both genders):

{
    "_id" : FRANCE,
    "count" : 59.0,
    "literary_work" : [ 
        {
            "type" : "POEM",
            "count" : 14.0
        }, 
        {
            "type" : "NOVEL",
            "count" : 34.0
        }, 
        {
            "type" : "SHORT STORY",
            "count" : 11.0
        }
    ]
}

标签： mongodb

解决方案

您的实现方式是正确的，但缺少一些东西：

  • 第一次错过计数$group
  • 在第一组计数的基础上，它可以计算整个计数literary_work
  • 并且$project您的查询不需要

更正了查询中的内容，

db.writers.aggregate([
  {
    $match: {
      gender: { $ne: "male" }
    }
  },
  {
    $group: {
      _id: {
        country_id: "$country_id",
        type: "$type"
      },
      // missed this
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.country_id",
      // this count will be on the base of first group count
      count: { $sum: "$count" },
      literary_work: {
        $push: {
          type: "$_id.type",
          // add count in inner count
          count: "$count"
        }
      }
    }
  },
  // corrected from country_id to _id
  {
    $sort: { "_id": 1 }
  }
])

工作游乐场：https ://mongoplayground.net/p/JWP7qdDY6cc

推荐阅读