MongoDB lookup and filter by foreign documents

Question

I've user collection which has role object which has roleId. I also have roles collection which has id. Now, for each role, I'd like to get the list of users.

Ex:

[
    {
        "name": "Scott",
        "isActive": true,
        "role": {
            "roleId": "123432"
        }
    },
    {
        "name": "John",
        "isActive": true,
        "role": {
            "roleId": "123432"
        }
    },
    {
        "name": "Scott",
        "isActive": true,
        "role": {
            "roleId": "556432"
        }
    }
]

Roles Data:

[
    {
        "id": "123432"
        "name": "admin",
        "type": "internal"
    },
    {
        "id": "556432"
        "name": "owner",
        "type": "external"
    },
    {
        "id": "556432"
        "name": "owner",
        "type": "internal"
    } 
]

Now I want to get all the roles of type internal and their related users:

So, the output should be,

[
    {
       "role": "123432",
        "users": [
            {
                "name": "Scott",
                "role": {
                    "roleId": "123432"
                }
            },
            {
                "name": "John",
                "role": {
                    "roleId": "123432"
                }
            }
        ],
        { 
            "role": "556432",
            "users": []
         }
    }
]

This is my aggregation in SpringBoort:

LookupOperation lookupOperation = LookupOperation.newLookup().from("roles").localField("roleId")
                .foreignField("_id").as("roles");

        AggregationOperation match = Aggregation.match(Criteria.where("type").is("internal"));

        Aggregation aggregation = Aggregation.newAggregation(lookupOperation, match);

        List<UserDTO> results = mongoTemplate.aggregate(aggregation, "users", UserDTO.class).getMappedResults();

But this is not working. The match filter ois working with main table (users) field. But it is not working with foreign collection(roles)

Can someone help me on this please?

Answer 1

AggregationOperation isActiveMatch= Aggregation.match(Criteria.where("isActive").is(true)); should be the first match.

LookupOperation lookupOperation = LookupOperation.newLookup().from("roles").localField("roleId")
                .foreignField("_id").as("roles");

AggregationOperation match = Aggregation.match(Criteria.where("type").is("internal"));

Aggregation aggregation = Aggregation.newAggregation(isActiveMatch,lookupOperation, match);

Update 1

You may expect like this,

db.user.aggregate([
  {
    "$lookup": {
      "from": "roles",
      "localField": "role.roleId",
      "foreignField": "id",
      "as": "roles"
    }
  },
  {
    $project: {
      roles: {
        "$filter": {
          "input": "$roles",
          "cond": {
            $eq: [
              "$$this.type",
              "internal"
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      roles: {
        $ne: []
      }
    }
  }
])

Working Mongo playground

Here you need to add two stages after lookup. First one is to filter the interval, 2nd is to eliminate empty roles array.

ProjectionOperation as =
        project()
            .and(
                ArrayOperators.Filter.filter("roles")
                    .as("role")
                    .by(ComparisonOperators.Eq.valueOf("role.type").equalTo("interval")))
            .as("roles");

I have added project stages, hope you can add match stage. The above code is not tested, but written based on working script.