Expand loading of linked documents to aggregation $lookup.

Currently both linked documents (@DBRef & @DocumentReference) are loaded as is, which means as their native raw representation of the link value. During the mapping process the framework detects those values and tries to resolve and map the linked documents.

This can lead to a degenerated performance and implies certain limitations:

• loading referenced values requires at least 1 additional server roundtrip.
• field level projections cannot be applied on nested paths.
• results cannot be sorted based on values in the reference.
• selection cannot limit results based on values in linked document.

However since MongoDB 5.1 the aggregation $lookup stage allows to join collections.

If we assume the following domain model.

class Order {

    Long id;
    
    @DBRef
    Inventory item;

    int price, quantity;
}

class Inventory {

    Long id;
    String sku;
    String description;
    int inStock;
}

The typical query would look like:

Query query = query(where("id").is("1"));

But it would fail as soon as there's a projection like the one listed below because there's no way to limit things in the referenced document which is not present at the state of execution.

Query query = query(where("id").is("1"));
query.fields().include("id", "price", "quantity", "item.sku");

However this would be possible using an aggregation, though it's is a bit more complex containing the join between the two collections.

MatchOperation $match = Aggregation.match(where("_id").is(order.id));

LookupOperation $lookup = Aggregation.lookup()
    .from("inventory")
    .localField("item.$id")
    .foreignField("_id")
    .pipeline(
        Aggregation.project("sku") 
    ).as("item");

ProjectionOperation $project = Aggregation.project(Order.class) // include properties of order
    .and(ArrayOperators.arrayOf("$item").elementAt(0)).as("item"); // extract single element lookup result

Aggregation.newAggregation($lookupWithProject, $project)

We should explore if and to what extend it is possible to rewrite a given query into its aggregation counterpart using $lookup to resolve references and how to express that desire.
Maybe we can draw some inspiration from JPA Entity Graphs in that regard.

Start with the query and mapping paths that handle @DBRef and @DocumentReference, then compare them with Aggregation.lookup and MongoDB 5.1's join constraints. Done means establishing whether queries can be rewritten to use $lookup and defining how callers express that behavior, including projections and filtering on referenced fields.