在MongoDB中聚合双嵌套数组的文档-Java 学习之路

我正在尝试计算具有不同条件的文档 . 在这里，我有这样简化的文本表（文件）：

{ 
  "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”：

how many documents on stage: "0" contains userID in answeredBy OR skippedBy arrays (I called this Document "Answered")
how many documents on stage: "0" doesn't contain userID both in answeredBy AND in skippedBy arrays (I called this Document "Unanswered")
how many documents with stage: "1" have in answers array at least ONE array readBy which doesn't contains user (I called it "UnRead" Document)

所以我试图以多种方式实现它，但最困难的部分是迭代数组 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 } } },
 ]);

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"}}}
])

回复于 2024-05-03T13:27:23+08:00

在MongoDB中聚合双嵌套数组的文档

1 回答

相关问题