Aggregate documents in MongoDB of double nested arrays

Question

I am trying to count documents with different conditions. Here I have such simplified table of texts(documents):

{ 
  "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] },       
  ]
};

And I want to count in one query per appropriate user Id, stage and teamID (so first $match must be per teamId and stages: "0" or "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)

So I tried to achieve it in many ways, but the most difficult part is to iterate through nested arrays (readBy) of array answers and find which one doesn't contain appropriate user and count this document as UNREAD.

Possible results:

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

or

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

I am stuck after writing this query and don't know how to iterate through readBy arrays:

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

Here is my working solution. Thank you for everyone who tried to solve it and helped me.

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

Maybe I should find a better solution, but currently this is what I need.

Answer 2

try this, I am assuming 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"}}}
])