MongoDB中的聚集字段

Question

我有两个MongoDb集合：

薄膜

{ _id: ObjectId("60644a81655e8216acf44b6e"), name: 'Django' }
{ _id: ObjectId("60647242c31ba840a0479221"), name: 'Kill Bill' }

筛选

{ _id: ObjectId("60644598655e8216acf44b6a"),
  name: 'Django',
  reviews: '1150',
  cinema: '01' }
{ _id: ObjectId("606445c7655e8216acf44b6b"),
  name: 'Django',
  reviews: '2130',
  cinema: '02' }
{ _id: ObjectId("606445c7655e8216acf44b6c"),
  name: 'Django',
  reviews: '2102',
  cinema: '03' }
{ _id: ObjectId("60647218c31ba840a047921f"),
  name: 'Kill Bill',
  reviews: '2000',
  cinema: '01' }
{ _id: ObjectId("60647218c31ba840a0479220"),
  name: 'Kill Bill',
  reviews: '587',
  cinema: '02' }

我想在电影集中添加一个新的聚合字段total_reviews，该字段是对一部电影的评论总数的总和；例如：

薄膜

{ _id: ObjectId("60644a81655e8216acf44b6e"), name: 'Django', total_reviews: "3382" }
{ _id: ObjectId("60647242c31ba840a0479221"), name: 'Kill Bill', total_reviews: "2587"}

有人能帮我吗？谢谢..。

Answer 1

我更改了合并时的on子句，它可以工作。

db.film.aggregate([
   {$lookup: {from: "screening", localField: "_id", foreignField: "film_id", as: "screening"}},
   {$set: {total_reviews : {$sum : "$screening.review"}}},
   {$project : {id:0, screening:0}},
   { $merge: { into: { db: "test", coll: "film" }, on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
   ])

但是，如果插入新的筛选文档，则字段total_reviews不会自动更新。有一种方法(比如SQL触发器)来自动化这个函数？

Answer 2

这是一个正确的解决方案，提供了4个阶段：

db.film.aggregate([
   {$lookup: {from: "screening", localField: "name", foreignField: "name", as: "screening"}},
   {$addFields: {total_reviews : {$sum : "$screening.review"}}},
   {$project : {id:0, screening:0}},
   {$merge: {into: {db: "test", coll: "film"}, on: "_id",
            whenMatched: "replace",whenNotMatched: "insert"}}
   ])

其结果是：

[{ name: 'Django', total_reviews: 5382 }, 
{ name: 'Kill Bill', total_reviews: 2587 }]