match operator query in mongo

Question

As part of aggregation query output I have data in the following form

[
    {
    "pid":"123",
    "title":"title1",
    "user" :[
        {
            "id":"user1",
            "title":"title1 user1",
            "jobs":[
                {
                "id":"123",
                "status" :"Active"
                },
                {
                "id":"456",
                "status" :"Active"
                }
            ]
        },
        {
            "id":"user2",
            "title":"title1 user2",
            "jobs":[
                {
                "id":"123",
                "status" :"Inactive"
                },
                {
                "id":"456",
                "status" :"Active"
                }
                
            ]
        }
        
    ]
    
    },
    
    {
        "pid":"456",
        "title":"title1",
        "user" :[
            {
                "id":"user1",
                "title":"title1 user1",
                "jobs":[
                    {
                    "id":"123",
                    "status" :"Active"
                    },
                    {
                    "id":"456",
                    "status" :"Inactive"
                    }
                ]
            },
            {
                "id":"user2",
                "title":"title1 user2",
                "jobs":[
                    {
                    "id":"123",
                    "status" :"Inactive"
                    },
                    {
                    "id":"456",
                    "status" :"Inactive"
                    }
                ]
            }
        ]
        
        },
        {
            "pid":"789",
            "title":"title1",
            "user" :[
                {
                    "id":"user1",
                    "title":"title1 user1",
                    "jobs":[
                        {
                        "id":"789",
                        "status" :"Active"
                        },
                        {
                        "id":"456",
                        "status" :"Inactive"
                        }
                    ]
                },
                {
                    "id":"user2",
                    "title":"title1 user2",
                    "jobs":[
                        {
                        "id":"789",
                        "status" :"Active"
                        },
                        {
                        "id":"456",
                        "status" :"Inactive"
                        }
                    ]
                }
            ]
            
            }
    
    ]

each of the user has multiple jobs . the pid ( the one at the root ) refers to the jobs.id in the users. Each user will have jobs matching the pid . I want to filter out all the user where the job.status != "Active" and the job.id == pid. In case of the pid 123 , i want to filter out user2 since the job id 123 has a status inactive. Similarly in case pid 456 both user1 and user2 will be filtered out since the status is Inactive for 456. For pid 789 both the users are ok since its PID status is active. Finally, I want to remove the entries PID where it does not have any users i.e 456 since it does not have any users.

How can I achieve this using the match filter

The answer at the end I am looking for is

[
    {
    "pid":"123",
    "title":"title1",
    "user" :[
        {
            "id":"user1",
            "title":"title1 user1",
            "jobs":[
                {
                "id":"123",
                "status" :"Active"
                },
                {
                "id":"456",
                "status" :"Active"
                }
            ]
        }
    ]
    
    },
    
        {
            "pid":"789",
            "title":"title1",
            "user" :[
                {
                    "id":"user1",
                    "title":"title1 user1",
                    "jobs":[
                        {
                        "id":"789",
                        "status" :"Active"
                        },
                        {
                        "id":"456",
                        "status" :"Inactive"
                        }
                    ]
                },
                {
                    "id":"user2",
                    "title":"title1 user2",
                    "jobs":[
                        {
                        "id":"789",
                        "status" :"Active"
                        },
                        {
                        "id":"456",
                        "status" :"Inactive"
                        }
                    ]
                }
            ]
            
            }
    
    ]

Answer 1

I have no clue what you are looking for, but this aggregation pipeline should show you the direction how to do it:

db.collection.aggregate([
   {
      $set: {
         user: {
            $map: {
               input: "$user",
               as: "user",
               in: {
                  $mergeObjects: [
                     "$$user",
                     {
                        jobs: {
                           $filter: {
                              input: "$$user.jobs",
                              as: "job",
                              cond: {
                                 $and: [
                                    { $ne: ["$$job.status", "Active"] },
                                    { $eq: ["$$job.id", "$pid"] }
                                 ]
                              }
                           }
                        }
                     }
                  ]
               }
            }
         }
      }
   },
   {
      $set: {
         user: {
            $filter: {
               input: "$user",
               cond: { $ne: ["$$this.jobs", []] }
            }
         }
      }
   },
   { $match: { user: { $ne: [] } } }
])

Mongo Playground