按行为体划分的mognodb聚集组

Question

我有以下电影收藏结构：

{
   "_id" : ObjectId,
   "title" : "movie-1",
   "actors" : [
      "actor-1",
      "actor-2",
      "actor-3",
   ],
   "categories" : [
     "category-1",
     "category-2"
   ]
 }

我想显示所有演员与相关电影和类别的结果，如下所示：

{
    "actor": "actor-1",
    "result": {
        "category-1": [ "movie-1", "movie-2" ],
        "category-2": [ "movie-1", "movie-4" ]
    }
}

我尝试了如下所示的聚合：

db.film.aggregate([
   {  $unwind: "$actors"  },
   {  $group: { 
        _id: "$actors", 
        data: {  $push: {  movie: "$title",   categories: "$categories"  } }
      }
   }, 
   {
      $project: {
        _id: 0,
        actor: "$_id",
        result: {
          $reduce: {
             input: "$data",
             initialValue: {},
             in: {
               $let: {
                  vars: { movie: "$$this.movie", categories: "$$this.categories" },
                  in: {
                     $arrayToObject: {
                         $map: {
                           input: "$$categories",
                           in: { k: "$$this",  v: "$$movie" }
                         }
                      }
                  }
               }
             }
           }
        }
      }
    }
 ])

但我得到的所有演员名单上只有一个类别的电影，如下所示：

{
   "actor" : "actor-1",
   "result" : {
     "category-1" : "movie-1",
     "category-2" : "movie-2",
     "category-3" : "movie-3"
   }
}

我该如何解决这个问题？提前谢谢。

Answer 1

我用大锤敲螺母 (c)

有些阶段可以由$reduce代替，在$project阶段内完成(欢迎批评和建议)

db.film.aggregate([
  {
    $unwind: "$actors"
  },
  {
    $group: {
      _id: "$actors",
      data: {
        $push: {
          movie: "$title",
          categories: "$categories"
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $unwind: "$data.categories"
  },
  {
    $group: {
      _id: {
        actors: "$_id",
        categories: "$data.categories"
      },
      movies: {
        $push: "$data.movie"
      }
    }
  },
  {
    $project: {
      _id: 0,
      actor: "$_id.actors",
      result: {
        k: "$_id.categories",
        v: "$movies"
      }
    }
  },
  {
    $group: {
      _id: "$actor",
      result: {
        $push: "$result"
      }
    }
  },
  {
    $project: {
      _id: 0,
      actor: "$_id",
      result: {
        $arrayToObject: "$result"
      }
    }
  },
  {
    $sort: {
      actor: 1
    }
  }
])

MongoPlayground

Answer 2

您可能需要在categories数组上执行另一个actors数组，然后按两个字段(即参与者字段和类别字段)对所有扁平的文档进行分组，以创建电影标题列表。

需要另一个组来形成结果字段。

下面的管道应该给出所需的结果：

db.film.aggregate([
    { "$unwind": "$actors" },
    { "$unwind": "$categories" },
    { "$group": { 
        "_id": { "actor": "$actors", "category": "$categories" },
        "movies": { "$push": "$title" }
    } },
    { "$group": { 
        "_id": "$_id.actor",
        "result": { 
            "$push": {
                "k": "$_id.category",
                "v": "$movies"
            }   
        }
    } },
    { "$addFields": {
       "result": { "$arrayToObject": "$result" }  
    } }
])