node.js - MongoDB 中双重嵌套数组的聚合文档

Question

我正在尝试计算不同条件下的文档。这里我有这样简化的文本表(文档):

{ 
  "teamId": "1",
  "stage": "0",
  "answeredBy": [userId_1, userId_2],
  "skippedBy": [userId_3],
  "answers": []
},
{ 
  "teamId": "1",
  "stage": "0",
  "answeredBy": [userId_2],
  "skippedBy": [userId_1],
  "answers": []
},
{ 
  "teamId" : "1",
  "stage": "0",
  "answeredBy": [userId_3],
  "skippedBy": [userId_2],
  "answers": []
},
{ 
  "teamId" : "1",
  "stage": "1",
  "answeredBy": [userId_3],
  "skippedBy": [userId_1, userId_2],
  "answers": [
              { "readBy": [userId_1] },
              { "readBy": [userId_1, userId_2] },
              { "readBy": [userId_3, userId_1] },       
  ]
},
{ 
  "teamId" : "1",
  "stage": "1",
  "answeredBy": [userId_3],
  "skippedBy": [userId_1, userId_2],
  "answers": [
              { "readBy": [userId_1] },
              { "readBy": [userId_1, userId_2] },
              { "readBy": [userId_3] },       
  ]
};

我想对每个适当的用户 ID、阶段和 teamID 进行一次查询计数(因此第一个 $match 必须是每个 teamId 和阶段:“0”或“1”:

• 舞台上有多少文档:“0”在answerBy或skippedBy数组中包含userID(我将此文档称为“Answered”)
• 舞台上有多少文档:“0”在answerBy和skippedBy数组中均不包含userID(我将此文档称为“未应答”)
• 有多少个阶段为“1”的文档在答案数组中至少有一个不包含用户的数组 readBy(我将其称为“未读”文档)

因此，我尝试了多种方式来实现这一目标，但最困难的部分是迭代数组 answers 的嵌套数组 (readBy)，并找到哪个不包含适当的用户，并将此文档计为 UNREAD。

可能的结果:

 {
   answered: 2,
   unanswered: 1,
   unread: 1,
 };

或

 [
   { _id: 'answered', count: 2 },
   { _id: 'unanswered', count: 1 },
   { _id: 'unread', count: 1 }
 ]

编写此查询后我陷入困境，并且不知道如何迭代 readBy 数组:

db.texts.aggregate([
      { $match: {teamId: 1, $or: [{currStage: 0}, {currStage: 1}]}},
      { $project: { 'stage': { $switch: { branches: [
      { case: 
             { $and: [ { $eq: [ '$currStage', 0 ] },
             { $not: [ { $or: [ { $in: [ userId_1, '$answeredBy' ] },
             { $in: [ userId_1, '$skippedBy' ] } ] } ] } ] },
        then: 'unanswered'},
      { case: 
             { $and: [ { $eq: [ '$currStage', 0 ] },
             { $or: [ { $in: [ userId_1, '$answeredBy' ] },
             { $in: [ userId_1, '$skippedBy' ] } ] } ] },
        then: 'answered'},
      { case:
             { $and: [ { $eq: [ '$currStage', 1 ] },
             { $not: [ { $in: [ userId_1, '$answers.readBy' ] } ] } ] },
        then: 'unread'},
                 ] } } } },
      { $group: { _id: '$stage', count: { $sum: 1 } } },
 ]); 

Answer 1

试试这个，我假设 userid = userId_1

db.getCollection('answers').aggregate([
      { $match: {teamId: '1', $or: [{stage: '0'}, {stage: '1'}]}},
      {$project:{
          counts :{$cond: [
              {$or:[{$in:["userId_1", "$answeredBy"]}, {$in:["userId_1", "$skippedBy"]}]}, 
              {$literal:{answered: 1, unaswered: 0}}, 
              {$literal:{answered: 0, unaswered: 1}}
          ]},
          unread : {$cond:[
                  {$gt:[{$reduce: {
                      input: "$answers", 
                      initialValue: 1, 
                      in: {$multiply:["$$value", 
                          {$cond:[
                              {$in:["userId_1", "$$this.readBy"]},
                              {$literal: 0},
                              {$literal: 1}
                           ]}
                       ]}}},
                       0
                     ]},
                     {$literal: 1},
                     {$literal: 0}
               ]}

      }},
      {$group: {_id: null, answered: {$sum: "$counts.answered"}, unanswered: {$sum: "$counts.unanswered"}, unread: {$sum: "$unread"}}}
])