MongoDB java API aggregate $lookup and pipeline use variable

Question

I have 2 Collections assume as 1 to many relation, e.g. user and comments. requirement is to merge user with their latest comment as single object and return the list of the newly merged object as result.

aggregation works perfect in the mongo console

db.user.aggregate(
        {
            $addFields:{
                cId: {$toString: '$_id'},
                user: '$$ROOT'
            }
        },
        {$match: {'invitees': {$elemMatch: {'user.userId': '5e70e82532044a5e4e7cbc8d'}}}},
        {$lookup: {
                from: 'comment',
                let: {'cc': '$cId'},
                pipeline: [
                    {$match: {$expr: {$and: [{$eq: ['$$cc', '$userId']},{$gte: ['$time', '$$NOW']}]}}},
                    {$sort: {'time': -1}},
                    {$limit: 1}
                ],
                as: 'next'
            }
        },
        {$unwind: {path: '$next'}},
        {
            $project: {
                _id: 0,
                user: 1,
                next: 1,
                status: {
                    $map: {
                        input: {
                            $filter: {
                                input: '$invitees',
                                as: 'item',
                                cond: {
                                    $eq: ['$$item.user.userId', '$userId']
                                }
                            }
                        },
                        as: 'invitee',
                        in: '$$invitee.status'
                    }
                }
            }
        },
        {$unwind: {path: '$status'}},
        {$match: {$expr: {$in: ['$status', ['Accepted', 'Pending']]}}},
        {$sort: {status: 1}}
        )

I tried translating this to spring-data Aggregations but had issues with $toString. then I tried using mongodb java API Aggregations API to run this.

Arrays.asList(
        Aggregates.addFields(Arrays.asList(
            new Field<>("cId", Document.parse("{$toString: '$_id'}")),
            new Field<>("user", "$$ROOT"),
            new Field<>("userId", userId)
            )
        ),
        Aggregates.match(Filters.elemMatch("invitees", Filters.eq("user.userId", userId))),
        Aggregates.lookup(
            "comment",
            Collections.singletonList(new Variable<>("cc", "$cId")),
            Arrays.asList(
                Aggregates.match(
                    Filters.expr(
                        Filters.and(
                            Filters.eq("$$cc", "$userId")
                        ))),
                Aggregates.sort(Sorts.ascending("time")),
                Aggregates.limit(1)
            ),
            "next"
        )
    );

    AggregateIterable<Document> r = mongoClient
        .getDatabase("my-db")
        .getCollection("user")
        .aggregate(l);

receiving error:

Command failed with error 168 (InvalidPipelineOperator): 'Unrecognized expression '$$cc'' on server localhost:27017. The full response is {"ok": 0.0, "errmsg": "Unrecognized expression '$$cc'", "code": 168, "codeName": "InvalidPipelineOperator"}

using:

    <dependency>
      <groupId>org.mongodb</groupId>
      <artifactId>mongodb-driver</artifactId>
      <version>3.12.3</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mongodb/mongodb-driver-core -->
    <dependency>
      <groupId>org.mongodb</groupId>
      <artifactId>mongodb-driver-core</artifactId>
      <version>3.12.3</version>
    </dependency>


    <dependency>
      <groupId>org.mongodb</groupId>
      <artifactId>bson</artifactId>
      <version>3.12.3</version>
    </dependency>

• java version: 8
• mongod version: 4.2.2
• mongo shell version: 4.2.3

any suggestion is much appreciated.

Answer 1

I thought the key point is Filters.eq can not read variables which prefix with $, like $$cc or $userId. I try to rewrite this part and it can work.

Aggregates.match(
    Filters.expr(
        Filters.and(
             new Document("$eq", Arrays.asList("$userId", "$$cc"))
        )
    )
)