Chapter 2: $lookup Export to Language using Compass

The version of my Compass is 1.16.1 and when I try to Export To Language to Java about the $lookup on the last video Lecture: Basic Joins I receive an error: Unrecognized option to $lookup: let

What should I do?

1 Like

You can un-check “Use Builders” and it should work. I had the same issue and it worked for me.

5 Likes

That’s right @imremy! Thanks

Hi imremy,

your solution work perfectly, but its readability is low, so, there is a way to use the API:

public static <TExpression> Bson lookup(String from, @Nullable List<Variable< TExpression> > let, List<? extends Bson> pipeline, String as) ;

with builders? I can not instantiate the parameter let. Can you give an example?

Thanks.

Hi Pasquale_87090,

I had the same problem like you, but for me the document generated by compass without builders didn’t work at all.
After trying to get a valid List<Variable< TExpression> > let for the Aggregates.lookup method I realized that there is a third signature that fits better for the case, you should check it out.

Hope it helps.

I’ve using export but there is a problem with sorting, maybe you can help me with this?

You want to sort the comments, not the movies. You have only one anyway.

So should I do like this for sorting comments?

Arrays.asList( new Document("lookup", new Document("from", "comments") .append("let", new Document("id", "_id")) .append(“pipeline”, Arrays.asList(new Document("$match", new Document("$expr", new Document("$eq", Arrays.asList("$movie_id", “$$id”)))))) .append(“as”, “comments”)), new Document("$sort", new Document(“date”, -1L)));

You should use Compass to come up with the appropriate pipeline. The way you present it to us it is completely illegible to the lack of indentation. Since a pipeline is not destructive to your data you should try it to see what it does.

Hi Fisher093,
The solution I used is to add the ‘sort’ set to the ‘pipeline’ field of the ‘$lookup’ document. I just added this as the last element of the array:

new Document("$sort", new Document(“date”, -1L)

Loic.

pipeline.addAll(Arrays.asList(Aggregates.match(
                                 Filters.eq("_id", new ObjectId(movieId))), 
                                 Aggregates.lookup("comments", "_id", "movie_id", "comments"), 
                                 Aggregates.sort(Sorts.descending("comments.date")
                                 )
                )
);

This is my solution, however I tested that the output is sorted (logged the comments array) but couldn’t get the Create/Update Comments and Delete Comments code. Tests run fine.

@Santiago_11047 your implementation is not sorting correctly. This is why you are not getting those two labs validation codes.

What you are doing is sorting on the date of inner array of reviews. That is not what is expected.

N.

So it seems the only way to get the lookup working is using Documents.parse for the expression part, since even in the Suggested Solution they are using it there? I would have prefered to be able to use something along the lines of " pipeline.add(Aggregates.match(Filters.expr(Filters.eq("$movie_id","$$id"))));" but this apparently is not supported?

Edit: From comparing the resulting strings it seems the problem is that Filters.eq does not generate the “$eq”: … query. This is rather… strange?

I am sorry, can you help me understand the query again? If you mean that Filters.eq is not as same as $eq in mongodb query, then I believe it is similar to same as $eq.

Kanika

No I’m not sure what you mean with " then I believe it is similar to same as $eq ."

To reitterate what I mean:

I have set up two pipelines:

    final ArrayList<Bson> pipeline = new ArrayList<>();
    Bson exprMatch = Document.parse("{'$expr': {'$eq': ['$movie_id', '$$id']}}");
    pipeline.add(Aggregates.match(exprMatch));
    pipeline.add(Aggregates.sort(Sorts.descending("date")));
    final Bson lookup = Aggregates.lookup("comments", let, pipeline, "comments");

and

    final ArrayList<Bson> pipeline2 = new ArrayList<>();
    pipeline2.add(Aggregates.match(Filters.expr(Filters.eq("$movie_id","$$id"))));
    pipeline2.add(Aggregates.sort(Sorts.descending("date")));
    final Bson lookup2 = Aggregates.lookup("comments", let, pipeline2, "comments");

The first generates the query

{ "$lookup" : { "from" : "comments", "let" : { "id" : "$_id" }, "pipeline" : [{ "$match" : { "$expr" : { "$eq" : ["$movie_id", "$$id"] } } }, { "$sort" : { "date" : -1 } }], "as" : "comments" } }

The second:

{ "$lookup" : { "from" : "comments", "let" : { "id" : "$_id" }, "pipeline" : [{ "$match" : { "$expr" : { "$movie_id" : "$$id" } } }, { "$sort" : { "date" : -1 } }], "as" : "comments" } }

I would have expexted them to be equal… ( the difference is in the match part that the first generates $ eq :… and the second does not…

This is not syntactically correct. Let me look for more detailed explanation.

Kanika