How to reverse $Unwind inside nested objects in mongodb aggregation-CodePudding

I am working in a review application which has business and business has review and it can be done by many users and one user can do many reviews to the same business so my data looks like:

[
  {
    Name: "business name",
    "_id": "633efd0d6bc0e52519c7e107",
    "review": [
      {
        "user": "62e6459847a818151639398e",
        "reviewArray": [
          {
            "title": "review title 122",
            "description": "this is description of review 2 by another user",
            "isDeleted": true,
            "suspended": false
          },
          {
            "title": "review title",
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false
          }
        ]
      },
      {
        "user": "62e6459847a818151639391f",
        "reviewArray": [
          {
            "title": "review title 2",
            "description": "this is description of review 1 by another user",
            "isDeleted": false,
            "suspended": false
          },
          {
            "title": "review",
            "description": "this is description of review by another user",
            "isDeleted": false,
            "suspended": true
          }
        ]
      }
    ]
  },
  {
    Name: "business2",
    "_id": "633efd0d6bc0e52519c7e108",
    "review": [
      {
        "user": "62e6459847a818151639398e",
        "reviewArray": [
          {
            "title": "review title",
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false
          },
          {
            "title": "review title",
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false
          }
        ]       
      },
      {
        "user": "62e6459847a818151639391f",
        "reviewArray": [
          {
            "title": "review title of another review",
            "description": "this is description of review11 by another user",
            "isDeleted": true,
            "suspended": false
          },
          {
            "title": "review",
            "description": "this is description of review by another user",
            "isDeleted": false,
            "suspended": true
          }
        ]
      }
    ]
  }
]

I want the final result to be in same pattern but the reviews which has been deleted and are suspended do not want and if all the reviews are either deleted or suspended don't need the user info and review array in final result as :

[
  {
    "Name": "business name",
    "_id": "633efd0d6bc0e52519c7e107",
    "review": [
      {
        "reviewArray": [
          {
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false,
            "title": "review title"
          }
        ],
        "user": "62e6459847a818151639398e"
      },
      {
        "reviewArray": [
          {
            "description": "this is description of review 1 by another user",
            "isDeleted": false,
            "suspended": false,
            "title": "review title 2"
          }
        ],
        "user": "62e6459847a818151639391f"
      }
    ]
  },
  {
    "Name": "business2",
    "_id": "633efd0d6bc0e52519c7e108",
    "review": [
      {
        "reviewArray": [
          {
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false,
            "title": "review title"
          },
          {
            "description": "this is description of review 2 by another user",
            "isDeleted": false,
            "suspended": false,
            "title": "review title"
          }
        ],
        "user": "62e6459847a818151639398e"
      }
    ]
  }
]

I tried using mongodb aggregation using unwind and group but is not giving me accurate result. What will be the final mongodb aggregation querry for the final result as shown above?

CodePudding user response：

One option is to use $map and $filter:

db.collection.aggregate([
  {$project: {
      Name: 1,
      review: {
        $map: {
          input: "$review",
          as: "user",
          in: {reviewArray: {
              $filter: {
                input: "$$user.reviewArray",
                as: "review",
                cond: {
                  $and: [
                    {$eq: ["$$review.isDeleted", false]},
                    {$eq: ["$$review.suspended", false]}
                  ]
                }
              }
            },
            user: "$$user.user"
          }
        }
      }
  }},
  {$project: {
      Name: 1,
      review: {
        $filter: {
          input: "$review",
          cond: {$gt: [{$size: "$$this.reviewArray"}, 0]}
        }
      }
  }}
])

See how it works on the playground example