Spring data MongoDb Aggregation lookup from String to ObjectId

Question

I'm using Spring (boot) data 2.2.7 with mongodb 4.0. I've set 3 collections that I'm trying to join via an aggregation lookup operation.

catalog
stock
operations

catalog

{
    "_id" : ObjectId("5ec7856eb9eb171b72f721af"),
    "model" : "HX711",
    "type" : "DIGITAL",
....
}

mapped by

@Document(collection = "catalog")
public class Product implements Serializable {

    @Id
    private String _id;
    @TextIndexed
    private String model;
....

stock

{
    "_id" : ObjectId("5ec78573b9eb171b72f721ba"),
    "serialNumber" : "7af646bb-a5a8-4b86-b56b-07c12a625265",
    "bareCode" : "72193.67751691974",
    "productId" : "5ec7856eb9eb171b72f721af",
......
}

mapped by

@Document(collection = "stock")
public class Component implements Serializable {

    @Id
    private String _id;
    private String productId;
....

the productId field refers to the _id one in the catalog collection

operations

{
    "_id" : ObjectId("5ec78671b9eb171b72f721d3"),
    "componentId" : ""5ec78573b9eb171b72f721ba",
    .....

}

mapped by

public class Node implements Serializable {

    @Id
    private String _id;
    private String componentId;
....

the componentId field refers to the _id one in the stock collection

I want to query operations or stock collection to retreive the corresponding Node or Component object list ordered by the Product.model field (in the catalog collection.)

While the goal is to code in Java I've tried to make the request first in the Mongo shell but I can't even get it working as I'm trying to join (lookup) a string with an ObjectId : Node.componentId -> Component._id Component.productId -> Product._id

For the relationship Component(stock) -> Product(Catalog) I've tryed

LookupOperation lookupOperation = LookupOperation.newLookup()
        .from("catalog")
        .localField("productId")
        .foreignField("_id")
        .as("product");

TypedAggregation<Component> agg =
        Aggregation.newAggregation(
                Component.class,
                lookupOperation
        );


AggregationResults<Component> results = mongoTemplate.aggregate(agg, "stock", Component.class);
return results.getMappedResults();

but it returns the whole components records without product info.

[{"_id":"5ec78573b9eb171b72f721b0","uuId":"da8800d0-b0af-4886-80d1-c384596d2261","serialNumber":"706d93ef-abf5-4f08-9cbd-e7be0af1681c","bareCode":"90168.94737714577","productId":"5ec7856eb9eb171b72f721a9","created":"2020-05-22T07:55:31.66","updated":null}, .....]

thanks for your help.

Note: In addition to @Valijon answer to be able to get the result as expected the returned object must include a "product" property either nothing is returned (using JSON REST service for example)

public class ComponentExpanded implements Serializable {

    private String product;
....

with

AggregationResults<ComponentExpanded> results =
        mongoTemplate.aggregate(agg,mongoTemplate.getCollectionName(Component.class), ComponentExpanded.class);

Valijon Valijon · Accepted Answer · 2020-05-22T22:31:10

The problem resides in the mismatch of types between productId and_id as you have observed.

To join such data, we need to perform uncorrelated sub-queries and not every "new" feature makes it immediately into abstraction layers such as spring-mongo.

Try this:

Aggregation agg = Aggregation.newAggregation(l -> new Document("$lookup",
    new Document("from", mongoTemplate.getCollectionName(Product.class))
        .append("let", new Document("productId", new Document("$toObjectId", "$productId")))
        .append("pipeline",
                Arrays.asList(new Document("$match",
                        new Document("$expr",
                                new Document("$eq", Arrays.asList("$_id", "$$productId"))))))
        .append("as", "product")),
    Aggregation.unwind("product", Boolean.TRUE));

AggregationResults<Component> results = mongoTemplate.aggregate(agg, 
    mongoTemplate.getCollectionName(Component.class), Component.class);
return results.getMappedResults();

MongoPlayground Check here how shell query looks like.

Note: For Java v1.7, you need to implement AggregationOperation like below:

AggregationOperation l = new AggregationOperation() {

    @Override
    public Document toDocument(AggregationOperationContext context) {
        return new Document(...); // put here $lookup stage
    }

};

Spring data MongoDb Aggregation lookup from String to ObjectId

1 Answers