arrays - 有条件地减少 Mongo 聚合中的两个数组字段

Question

我有一个如下所示的集合:

{
  "_id": 1,
  "user": "xyz",
  "sentence": "I watch movies and web series.",
  "nouns": [
    "movies",
    "web series"
  ],
  "verbs": [
    "watch"
  ]
},
{
  "_id": 2,
  "user": "xyz",
  "sentence": "movies are good way to relax",
  "nouns": [
    "movies"
  ],
  "verbs": [
    "relax"
  ]
}

有两个数组字段， nouns和 verbs对于每个用户的句子。我想按 user 对文档进行分组字段并分别计算 nouns 中每个不同元素的数量和 verbs数组。我已经尝试了以下查询(如果你不想，你可以跳到这个聚合的最后阶段):

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      sentence: {
        $push: "$sentence"
      },
      verbs: {
        $push: "$verbs"
      },
      nouns: {
        $push: "$nouns"
      }
    }
  },
  {
    $project: {
      verbs: {
        $reduce: {
          input: "$verbs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      nouns: {
        $reduce: {
          input: "$nouns",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      sentence: 1
    }
  },
  {
    $project: {
      nouns_count_temp: {
        $map: {
          input: "$nouns",
          as: "c",
          in: {
            k: "$$c",
            v: 1
          }
        }
      },
      verbs_count_temp: {
        $map: {
          input: "$verbs",
          as: "c",
          in: {
            k: "$$c",
            v: 1
          }
        }
      },
      sentence: 1
    }
  },
  {
    $project: {
      sentence: 1,
      noun_count: {
        $reduce: {
          input: "$nouns_count_temp",
          initialValue: [],
          in: {
            $cond: [
              {
                $in: [
                  {
                    k: "$$this.k",
                    v: "$$this.v"
                  },
                  "$$value"
                ]
              },
              {
                $add: [
                  "$$value.$.v",
                  1
                ]
              },
              {
                $concatArrays: [
                  "$$value",
                  [
                    {
                      k: "$$this.k",
                      v: "$$this.v"
                    }
                  ]
                ]
              }
            ]
          }
        }
      },
      verb_count: {
        $reduce: {
          input: "$verbs_count_temp",
          initialValue: [],
          in: {
            $cond: [
              {
                $in: [
                  {
                    k: "$$this.k",
                    v: "$$this.v"
                  },
                  "$$value"
                ]
              },
              {
                $add: [
                  "$$value.$.v",
                  1
                ]
              },
              {
                $concatArrays: [
                  "$$value",
                  [
                    {
                      k: "$$this.k",
                      v: "$$this.v"
                    }
                  ]
                ]
              }
            ]
          }
        }
      }
    }
  }
])

我在聚合的最后状态面临问题。我想知道，是否有更好的使用方法 $cond在 $reduce ，以便我可以有条件地减少数组。

我的预期输出如下:

{
  "_id": "xyz",
  "noun_count": {
    "movies": 2,
    "web series": 1
  },
  "sentence": [
    "I watch movies and web series.",
    "movies are good way to relax"
  ],
  "verb_count": {
    "relax": 1,
    "watch": 1
  }
}

这是 MongoPlayGroundLink ，我已经尝试过。

Answer 1

不幸的是，我们无法构建动态 key:value对象内 $reduce运算符(operator)。

解决方法:我们联合nouns和 verbs在单个数组中并计算它们重复的次数。

db.collection.aggregate([
  {
    $group: {
      _id: "$user",
      sentence: {
        $push: "$sentence"
      },
      verbs: {
        $push: "$verbs"
      },
      nouns: {
        $push: "$nouns"
      }
    }
  },
  {
    $project: {
      sentence: 1,
      verbs: {
        $reduce: {
          input: "$verbs",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      },
      nouns: {
        $reduce: {
          input: "$nouns",
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              "$$this"
            ]
          }
        }
      }
    }
  },
  {
    $addFields: {
      mix: {
        $concatArrays: [
          "$verbs",
          "$nouns"
        ]
      }
    }
  },
  {
    $unwind: "$mix"
  },
  {
    $group: {
      _id: {
        user: "$_id",
        word: "$mix"
      },
      count: {
        $sum: 1
      },
      sentence: {
        $first: "$sentence"
      },
      verbs: {
        $first: "$verbs"
      },
      nouns: {
        $first: "$nouns"
      }
    }
  },
  {
    $group: {
      _id: "$_id.user",
      data: {
        $push: {
          k: "$_id.word",
          v: "$count"
        }
      },
      verbs: {
        $first: "$verbs"
      },
      nouns: {
        $first: "$nouns"
      },
      sentence: {
        $first: "$sentence"
      }
    }
  },
  {
    $project: {
      _id: 1,
      sentence: 1,
      noun_count: {
        $arrayToObject: {
          $filter: {
            input: "$data",
            as: "data",
            cond: {
              $in: [
                "$$data.k",
                "$nouns"
              ]
            }
          }
        }
      },
      verb_count: {
        $arrayToObject: {
          $filter: {
            input: "$data",
            as: "data",
            cond: {
              $in: [
                "$$data.k",
                "$verbs"
              ]
            }
          }
        }
      }
    }
  }
])

MongoPlayground | Alternative solution

注: MapReduce 解决方案比聚合慢