spring data mongodb lookup with dbref

Question

I am using MongoDB (3.4) with Spring Data. And I am getting incorrect results when I use lookup operation. The collection operation are as follows:

@Document
public class Employee {
    @Id
    String id;
    String name;
    @DBRef
    EmpAddress address;

    String city;     //redundant, but intentionally 
    //getters and setters
}

@Document
public class EmpAddress {
    @Id
    String id;

    String city;
    //getters and setters
}

DB is populated with the following data

Employees [{ "_id" : { "$oid" : "597b557cfe4b9104e8409f2a"} , "_class" : "com.example.Employee" , "name" : "PKM1" , "city" : "NYC" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f25"}}}, { "_id" : { "$oid" : "597b557cfe4b9104e8409f2b"} , "_class" : "com.example.Employee" , "name" : "PKM2" , "city" : "SFO" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f26"}}}, { "_id" : { "$oid" : "597b557cfe4b9104e8409f2c"} , "_class" : "com.example.Employee" , "name" : "PKM3" , "city" : "LA" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f27"}}}, { "_id" : { "$oid" : "597b557cfe4b9104e8409f2d"} , "_class" : "com.example.Employee" , "name" : "PKM4" , "city" : "SFO" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f28"}}}, { "_id" : { "$oid" : "597b557cfe4b9104e8409f2e"} , "_class" : "com.example.Employee" , "name" : "PKM5" , "city" : "NYC" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f29"}}}]



Cities [{ "_id" : { "$oid" : "597b557bfe4b9104e8409f25"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"}, { "_id" : { "$oid" : "597b557bfe4b9104e8409f26"} , "_class" : "com.example.EmpAddress" , "city" : "SFO"}, { "_id" : { "$oid" : "597b557bfe4b9104e8409f27"} , "_class" : "com.example.EmpAddress" , "city" : "LA"}, { "_id" : { "$oid" : "597b557bfe4b9104e8409f28"} , "_class" : "com.example.EmpAddress" , "city" : "SFO"}, { "_id" : { "$oid" : "597b557bfe4b9104e8409f29"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"}]

When I do lookup as follows

MatchOperation match = Aggregation.match(Criteria.where("name").is("PKM1"));
LookupOperation lookup = LookupOperation.newLookup().from("empAddress").localField("address.$id").foreignField("id").as("emp_loc");
Aggregation aggregation = Aggregation.newAggregation(match, lookup);
AggregationResults<DBObject> result = mongoTemplate.aggregate(aggregation, "employee", DBObject.class);

The results is :

[{ "_id" : { "$oid" : "597b557cfe4b9104e8409f2a"} , "_class" : "com.example.Employee" , "name" : "PKM1" , "city" : "NYC" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f25"}} , "emp_loc" : [ { "_id" : { "$oid" : "597b557bfe4b9104e8409f25"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"} , { "_id" : { "$oid" : "597b557bfe4b9104e8409f26"} , "_class" : "com.example.EmpAddress" , "city" : "SFO"} , { "_id" : { "$oid" : "597b557bfe4b9104e8409f27"} , "_class" : "com.example.EmpAddress" , "city" : "LA"} , { "_id" : { "$oid" : "597b557bfe4b9104e8409f28"} , "_class" : "com.example.EmpAddress" , "city" : "SFO"} , { "_id" : { "$oid" : "597b557bfe4b9104e8409f29"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"}]}]

The above result is incorrect, since all cities are getting listed.

However, if I do lookup on another field I get the right result

LookupOperation lookup = LookupOperation.newLookup().from("empAddress").localField("city").foreignField("city").as("emp_loc");

The result is as follows :

[{ "_id" : { "$oid" : "597b557cfe4b9104e8409f2a"} , "_class" : "com.example.Employee" , "name" : "PKM1" , "city" : "NYC" , "address" : { "$ref" : "empAddress" , "$id" : { "$oid" : "597b557bfe4b9104e8409f25"}} , "emp_loc" : [ { "_id" : { "$oid" : "597b557bfe4b9104e8409f25"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"} , { "_id" : { "$oid" : "597b557bfe4b9104e8409f29"} , "_class" : "com.example.EmpAddress" , "city" : "NYC"}]}]

How do I get second result in when I lookup using id?

Possible duplicate of [Mongo how to $lookup with DBRef](https://stackoverflow.com/questions/40622714/mongo-how-to-lookup-with-dbref) — Olivier Maurel, Apr 10 '18 at 19:03

score 0 · Answer 1 · answered Sep 15 '17 at 10:33

0

It is a problem from DBref. Since mongoDB 3.4, cannot use DBRef in the aggregation pipeline, except in the $match stage.

The query returns all the results because the id is null, I had the same problem.

You should create manual references to prevent this problem.

You can read more about this in this answer: https://stackoverflow.com/a/41677055/4023844

answered Sep 15 '17 at 10:33

Juan L. Sánchez

11
3

The answer you linked to is wrong, there is a solution for doing $lookup on DBRefs field, [as detailed in my answer to a similar question.](https://stackoverflow.com/a/46240372/7495147) – Olivier Maurel Apr 10 '18 at 19:02

spring data mongodb lookup with dbref

1 Answers1