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.

Gendron

I'm also looking for this kind of scenario, if you get answer, please post. If there any references also,please post – Mandrel

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"))
        )
    )
)

Vasileior

Thanks for sharing. I forgot to update the question. I managed to make it work with different approaches. I mean mixing Spring Aggregation, Mongo Java Aggregation SDK as well as Document.parse I'll hopefully post my answer too – Gendron

@ArthurKazemi. can you post your answer please? – Brewmaster