For the below dataset I want to receive the documents in such a way that if the user has the role of manager and it is active then I want to ignore all the other records of that user with the role user but if manager with active false then I want all the records with role user it doesn't matter if it is active or not 2.If the record with role manager doesn't exist I want all the role user records for that user and vice versa

    [
      0:{
        role:"manager",
        user:"john",
        active:true
      },
      1:{
        role:"user",
        region:"us",
        user:"john",
        active:false
      },
      2:{
         role:"user",
        region:"czk",
        user:"john",
        active:false
      },
      3:{
        role:"user",
        region:"czk",
        user:"jane",
        active:false
      },
      4:{
        role:"user",
        region:"us",
        user:"jane",
        active:true
      },
      5:{
        role:"manager",
        user:"jane",
        active:false
      },
    ]

So in Above case index 0 and 3,4 will be fetched from database So far for achieving this output i'd tried this

db.collection.aggregate([
  {
    $match:{
      $or:[
           { $and: [{ role: manager }, { active: true }]},
           { $and: [{ role: user }, { active: true }] }
      ]
    }
  }   
])

but it does not give the expected output

CodePudding user response：

I am afraid this is not as straightforward. You will have to first group the documents by username, and then filter out the grouped documents array, according to your criteria. This is one way of doing it:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$user",
      "docs": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$addFields": {
      "filterStatus": {
        "$reduce": {
          "input": "$docs",
          "initialValue": {
            managerWithActiveTrue: false,
            managerWithActiveFalse: false,
            managerIsPresent: false,
            userIsPresent: false
          },
          "in": {
            "$mergeObjects": [
              "$$value",
              {
                managerWithActiveTrue: {
                  "$or": [
                    {
                      "$and": [
                        {
                          "$eq": [
                            "$$this.role",
                            "manager"
                          ]
                        },
                        "$$this.active"
                      ]
                    },
                    "$$value.managerWithActiveTrue"
                  ]
                },
                managerWithActiveFalse: {
                  "$or": [
                    {
                      "$and": [
                        {
                          "$eq": [
                            "$$this.role",
                            "manager"
                          ]
                        },
                        {
                          $not: "$$this.active"
                        }
                      ]
                    },
                    "$$value.managerWithActiveFalse"
                  ]
                },
                managerIsPresent: {
                  "$or": [
                    {
                      "$eq": [
                        "$$this.role",
                        "manager"
                      ]
                    },
                    "$$value.managerIsPresent"
                  ]
                },
                userIsPresent: {
                  "$or": [
                    {
                      "$eq": [
                        "$$this.role",
                        "user"
                      ]
                    },
                    "$$value.userIsPresent"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "docs": {
        "$switch": {
          "branches": [
            {
              "case": {
                "$or": [
                  "$filterStatus.managerWithActiveTrue",
                  {
                    $not: "$filterStatus.userIsPresent"
                  }
                ]
              },
              "then": {
                "$filter": {
                  "input": "$docs",
                  "as": "item",
                  "cond": {
                    "$eq": [
                      "$$item.role",
                      "manager"
                    ]
                  }
                }
              }
            },
            {
              "case": {
                "$or": [
                  "$filterStatus.managerWithActiveFalse",
                  {
                    $not: "$filterStatus.managerIsPresent"
                  }
                ]
              },
              "then": {
                "$filter": {
                  "input": "$docs",
                  "as": "item",
                  "cond": {
                    "$eq": [
                      "$$item.role",
                      "user"
                    ]
                  }
                }
              }
            }
          ],
          default: "$docs"
        }
      }
    }
  },
  {
    "$unwind": "$docs"
  },
  {
    "$replaceRoot": {
      "newRoot": "$docs"
    }
  }
])

In this query, we first group the documents by username, then calculate a new field named filterStatus, in which store of the four given conditions which ones apply to the user. Finally, we filter the array using the filterStatus field in $switch, and then we unwind the array, and bring the docs to the root again, using $replaceRoot.

This is the playground link.