2

Tying to get a lookup working with the async java driver to join 2 collections. But I can't figure out the correct syntax to use as I can't find any documentation on what the syntax is using the Let (variables) version of the $lookup command is to work properly.

Here is what using (and it doesn't find any matches):

final List<Bson> lookUppipeline = new ArrayList<>();
final List<Variable<?>> variables = Arrays.asList(new Variable<>("id", "$_id"));
lookUppipeline.add(match(eq("object_id", "$$id")));
final Bson lookup = lookup("values_collection", variables, lookUppipeline, "tag_values");
final AggregateIterable<ApiJsonObject> findIter = info_collection.aggregate(Arrays.asList(lookup, skip(0), limit(1_000), project(exclude(EXCLUDES_LIST))));

Basically the $_id in the info_collection is equal to the object_id of the values_collection.

Do I need to create the Variable differently or the match equals? If I use the foreign key version of the lookup method it works but I need to use pipelines as I have other things to add to it.

There are examples using the sync driver but not with the async driver.

The syntax from a mongodb query is

db.info_collection.aggregate([
   {
      $lookup:{
          from: "values_collection",
          let: { id: "$_id" },
          pipeline: [
             { $match:
                { $expr:
                   { $and:
                      [
                        { $eq: [ "$object_id",  "$$id" ] }
                      ]
                   }
                }
             }
          ],
          as: "tag_values"
        }
   }
])

Here is some sample data (trimmed down but all relevant):

info_collection:

[
    {"_id":"cce9ec95-dd03-4066-89c5-86227be70503", "Name":"Object1" }
    {"_id":"cce9ec95-dd03-4066-89c5-86227be70503", "Name":"Object2" }
    {"_id":"cce9ec95-dd03-4066-89c5-86227be70503", "Name":"Object3" }
    {"_id":"cce9ec95-dd03-4066-89c5-86227be70503", "Name":"Object4" }
    {"_id":"cce9ec95-dd03-4066-89c5-86227be70503", "Name":"Object5" }
]

values_collection:

[
    {"object":"cce9ec95-dd03-4066-89c5-86227be70503", "_id":{"$oid":"5e1cee26610b668017537cc2"}, "Value":"test1", "Tag":1},
    {"object":"6494bcec-c94f-4421-9f5a-84a76edda8fd", "_id":{"$oid":"5e1cee26610b668017537cc4"}, "Value":"test2", "Tag":1},
    {"object":"ea40aaf7-1d7c-4bf2-8a98-93cfbf62035d", "_id":{"$oid":"5e1cee26610b668017537cc6"}, "Value":"test3", "Tag":1},
    {"object":"7556a86d-4962-4220-8a77-10655e8e4376", "_id":{"$oid":"5e1cee26610b668017537cc8"}, "Value":"test4", "Tag":1},
    {"object":"f78d4302-0756-47bb-aaff-c93744d147fe", "_id":{"$oid":"5e1cee26610b668017537cca"}, "Value":"test5", "Tag":1},
    {"object":"06ade084-3e2a-42eb-9063-5059c447e518", "_id":{"$oid":"5e1cee26610b668017537ccc"}, "Value":"test6", "Tag":1}
]
Marc Olin
  • 59
  • 7
  • Can you post please your sample data? Also, ty to change `lookUppipeline.add(match(eq("object_id", "$$id")));` to 'lookUppipeline.add(match(expr(eq("object_id", "$$id"))));' [example](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#specify-multiple-join-conditions-with-lookup) – Valijon Jan 13 '20 at 21:27
  • I think executing Async driver query (Should throw `query failed: unknown top level operator: $eq` error) is different fom sync driver query – Valijon Jan 13 '20 at 21:53
  • Adding the expr (com.mongodb.client.model.Filters.expr) to the query made it return all rows in the `values_collection` in the `tag_values` array. Even if I added other eq filters that should reduce the data it didn't work. I'll add sample data to the question. – Marc Olin Jan 13 '20 at 22:34
  • So when i run this (without the expr) `lookUppipeline.add(match(eq("Value", "test1")));` it works correct. With the expr `lookUppipeline.add(match(expr(eq("Value", "test1"))));` it does not (it returns all the rows from the values_collection even though only one should). Based on previous work with the async driver expr is not necessary. I still think it is something in the way to get the async driver to under stand "$$id" is not a string but represents a field in the variables parameter. – Marc Olin Jan 13 '20 at 22:55
  • Are you sure you want to use the MongoDB Java Async Driver? The [documentation](https://mongodb.github.io/mongo-java-driver/3.12/driver-async/) says: _The callback-based Async Java Driver has been deprecated in favor of the MongoDB Reactive Streams Java Driver._ – prasad_ Jan 14 '20 at 04:05
  • Changing to the Reactive Streams driver would be large change to move from a call back implementation to a stream publish/subscribe implementation. Also it is built ontop of the async driver so I don't know if this would be solved. I'm guessing there is a way to do this with the async driver, I'm just missing something in the syntax. – Marc Olin Jan 14 '20 at 15:24

1 Answers1

1

Figured out the answer, I guess with aggregates you can't use the short hand helper functions but must pass in parameters as Documents.

This includes the extra equal parameter for why I couldn't use the other lookup function.

final List<Variable<?>> variables = Arrays.asList(new Variable<>("key", "$_id"));
final List<Bson> pipeline = Arrays.asList(match(expr(new Document("$and",
    Arrays.asList(new Document("$eq", Arrays.asList("$object_id", "$$key")),
                  new Document("$eq", Arrays.asList("$tag", tagId)),
                  new Document("$eq", Arrays.asList("$value", value)))))),
    project(fields(include("tag_values", "_id"))));
final Bson lookup = lookup("values_collection", variables, pipeline, "tag_values");
final AggregateIterable<ApiJsonObject> findIter = info_collection.aggregate(Arrays.asList(lookup, unwind("$tag_values"), skip(0), limit(1_000), project(exclude(EXCLUDES_LIST))));
Marc Olin
  • 59
  • 7
  • Was usefull, but I had to specify the type of the variables list, if not the compiler gave me an error final List> variables = Arrays.asList(new Variable<>("key", "$_id")); – Paolo Biavati Mar 23 '23 at 13:57