mongodb - 对数组中的不同对象值进行分组？

Question

需要 MongoDB 查询/聚合:如果我有一个数组，我如何根据数组中的不同值对文档进行分组？示例:

如果我有这些对象:

> db.respondents.insert({person: 1, responses: [{question: 'How old are you?', response: '18-40 yrs'}, {question: 'What is the brand of your car?', response: 'Fiat'} ] } )
> db.respondents.insert({person: 2, responses: [{question: 'How old are you?', response: '18-40 yrs'}, {question: 'What is the brand of your car?', response: 'Volvo'} ] } )
> db.respondents.insert({person: 3, responses: [{question: 'How old are you?', response: '41-65 yrs'}, {question: 'What is the brand of your car?', response: 'Volvo'} ] } )
> db.respondents.insert({person: 4, responses: [{question: 'How old are you?', response: '41-65 yrs'}, {question: 'What is the brand of your car?', response: 'Volvo'} ] } )

并想编写一个查询，告诉我每个年龄组(又名分割问题)受访者拥有哪些汽车品牌(又名基本问题)？

所以答案应该告诉我:

“18-40 岁”年龄段的 1 人回答“菲亚特”问题“您的汽车是什么品牌？”

“18-40 岁”年龄段的 1 人回答“沃尔沃”问题“您的汽车是什么品牌？”

“41-65 岁”年龄段的 2 人回答“沃尔沃”问题“你的车是什么牌子的？”

和现实生活:

• 有超过 100.000 名受访者
• 每个受访者大约有 30 个“回复”
• 使用 MongoDb 3.0.9

我已经尝试了很多方法，但不会因为我的失败而让你厌烦....

Answer 1

遗憾的是您没有 MongoDB 3.2，因为像 $arrayElemAt 这样的运算符和 $filter使用单个 $group 使这个过程变得简单阶段:

db.respondents.aggregate([
  { "$match": { 
    "responses.question": { 
      "$all": [
        "How old are you?",
        "What is the brand of your car?"
      ]
    } 
  }},
  { "$group": {
    "_id": {
      "age": {
        "$arrayElemAt": [
          { "$map": {
            "input": { "$filter": {
              "input": "$responses",
              "as": "res",
              "cond": {
                "$eq": [ "$$res.question", "How old are you?" ]
              }
            }},
            "as": "res",
            "in": "$$res.response"
          }},
          0
        ]
      },
      "car": {
        "$arrayElemAt": [
          { "$map": {
            "input": { "$filter": {
              "input": "$responses",
              "as": "res",
              "cond": {
                "$eq": [ "$$res.question", "What is the brand of your car?" ]
              }
            }},
            "as": "res",
            "in": "$$res.response"
          }},
          0
        ]
      }
    },
    "count": { "$sum": 1 }
  }}
])

在早期版本中，您需要 $unwind内容，然后通过 $cond 有条件地选择所需的响应值:

db.respondents.aggregate([
  { "$match": { 
    "responses.question": { 
      "$all": [
        "How old are you?",
        "What is the brand of your car?"
      ]
    } 
  }},
  { "$unwind": "$responses" },
  { "$match": { 
    "responses.question": { 
      "$in": [
        "How old are you?",
        "What is the brand of your car?"
      ]
    } 
  }},
  { "$group": {
    "_id": "$_id",
    "age": {
      "$max": {
        "$cond": [
          { "$eq": [ "$responses.question", "How old are you?" ] },
          "$responses.response",
          null
        ]
      }
    },
    "car": {
      "$max": {
        "$cond": [
          { "$eq": [ "$responses.question", "What is the brand of your car?" ] },
          "$responses.response",
          null
        ]
      }
    }
  }},
  { "$group": {
    "_id": {
      "age": "$age",
      "car": "$car"
    },
    "count": { "$sum": 1 }
  }}
])

但当然是很有可能的，常见的结果是:

{ "_id" : { "age" : "41-65 yrs", "car" : "Volvo" }, "count" : 2 }
{ "_id" : { "age" : "18-40 yrs", "car" : "Volvo" }, "count" : 1 }
{ "_id" : { "age" : "18-40 yrs", "car" : "Fiat" }, "count" : 1 }