spring-data-mongodb: findAll() with list of input documents and search parameter for the embedded DBRef Document

Question

I am using spring-data-mongo and trying to access the dbref objects with params. My project looks like this:

My models are as follows:

i. First Document is "Cars"

@Document("cars")
class CarDocument {
   @Id
   private String id;
   private String name;
   private String madeInCountry;
   private String model;
   private String madeInYear;
}

ii. Second document is "tools"

Document("tools")
class ToolDocument {
   @Id
   private String id;
   private String name;
   private String madeInCountry;
   private String madeInYear;
   private List<UsedIn> usedIn = new ArrayList<>();
}

iii. The third is embedded model "UsedIn" in (ii.) Third embedded model represents where tools are used to make cars in the manufacturing house.

class UsedIn {
   @DBRef
   private CarDocument car;
   private DateTime usedDate;
   private String usedByUsername;
}

My DAO's are as follows:

public interface CarDAO extends MongoRepository<CarDocument, String>
{
    public CarDocument findByMadeInCountry(String madeInCountry);
}
public interface ToolDAO extends MongoRepository<ToolDocument, String>
{
    public ToolDocument findByMadeInCountry(String madeInCountry);
}

Now I need list of all the "Tools" which is used in the specific car. Say a. when car is madeInCountry: "germany" and b. tool is madeInCountry: "germany"

I see that we can't apply search directly on DBRef documents. like :

String madeInCountry = "germany";
toolDAO.findByMadeInCountryAndUsedInCarMadeInCountry(madeInCountry,madeInCountry);

I get this error:

"Invalid path reference car.madeInCountry! Associations can only be pointed to directly or via their id property!"

How to this?

Do I need to do two DAO calls? Say i. first get all the cars with madeInCountry is germany

String madeInCountry = "germany";
carDAO.findByMadeInCountry(madeInCountry);

ii. findTools by the list of carDocuments and String.

I dont know, how to call this dao with list of CarDocuments and madeInCountry String ?

Do I need to use some $lookup functionality?

Thanks

I don't think even making two calls will help you. The second call that you need is a call over embedded dbref with list of car documents value. The query will look for a match and return all the car documents when a match is found for a tool document. Meaning you will get tool documents which is made in Germany that has atleast one used in - cardocument made in Germany. Let me know what you think. — s7vr, Mar 10 '17 at 10:52
A possible solution would be replace the `car` field (Remove the `DBref ` annotation too) in the `UsedIn` with `carId` field, something like `{"carId":"id", "usedDate":"date", "usedByUsername":"user"}`. You can now use aggregation with `$match` on `madeInCountry` on tools collection followed by `$lookup` to join to cars collection and `$match` on the `madeInCountry` on the joined collection — s7vr, Mar 11 '17 at 01:21

score 3 · Accepted Answer · edited May 23 '17 at 11:54

You can try below aggregation.

Update your UsedIn class to below.

 private Long carId;
 private CarDocument car;
 private Date usedDate;
 private String usedByUsername;

Mongo Shell Query:

db.tools.aggregate([{
    "$match": {
        "madeInCountry": "germany"
    }
}, {
    "$unwind": "$usedIn"
}, {
    "$lookup": {
        "from": "cars",
        "localField": "usedIn.carId",
        "foreignField": "_id",
        "as": "usedIn.car"
    }
}, {
    "$unwind": "$usedIn.car"
}, {
    "$match": {
        "usedIn.car.madeInCountry": "germany"
    }
}, {
    "$group": {
        _id: "$_id",
        usedIns: {
            "$push": "$usedIn"
        }
    }
}])

Spring Aggregation Code:

 Criteria toolQuery = Criteria.where("madeInCountry").in("germany");
 MatchOperation toolMatchOperation = new MatchOperation(toolQuery);
 LookupOperation lookupOperation = LookupOperation.newLookup().
                from("cars").
                localField("usedIn.carId").
                foreignField("_id").
                as("usedIn.car");
 Criteria carQuery = Criteria.where("usedIn.car.madeInCountry").is("germany");
 MatchOperation carMatchOperation = new MatchOperation(carQuery);

 TypedAggregation<ToolDocument> aggregation = Aggregation.newAggregation(ToolDocument.class, toolMatchOperation, Aggregation.unwind("usedIn"), lookupOperation, Aggregation.unwind("usedIn.car"), carMatchOperation,
                Aggregation.group("id").push("usedIn").as("usedIn"));
 List<ToolDocument> results = mongoTemplate.aggregate(aggregation, ToolDocument.class).getMappedResults();

Methods to load the data.

Car Data

public void saveCar() {
    carDao.deleteAll();

    CarDocument carDocument1 = new CarDocument();
    carDocument1.setId(1L);
    carDocument1.setName("audi");
    carDocument1.setMadeInCountry("germany");

    carDao.save(carDocument1);
}

Tool Data

public void saveTool() {

    toolDao.deleteAll();

    ToolDocument toolDocument1 = new ToolDocument();
    toolDocument1.setId(1L);
    toolDocument1.setName("wrench");
    toolDocument1.setMadeInCountry("germany");

    UsedIn usedIn1 = new UsedIn();
    usedIn1.setCarId(1L);
    usedIn1.setUsedByUsername("user");
    usedIn1.setUsedDate(new Date());

    List<UsedIn> usedIns1 = new ArrayList<>();
    usedIns1.add(usedIn1);

    toolDocument1.setUsedIn(usedIns1);

    toolDao.save(toolDocument1);
}

Update:

To answer your question about accessing the DBRefs

ii. findTools by the list of carDocuments and String.

I dont know, how to call this dao with list of CarDocuments and madeInCountry String ?

 public List<ToolDocument> findByMadeInCountryAndUsedInCarIn(String madeInCountry, List<CarDocument> carDocuments);

Like I noted in the first comment the second call that you need is a call over embedded dbref with list of car documents value. The query will look for a match and return all the car documents when a match is found for a tool document. Meaning you will get tool documents which is made in Germany that has atleast one used in - cardocument made in Germany.

Sharded update($lookup equalivent)( Idea taken from here MongoDB to Use Sharding with $lookup Aggregation Operator )

List<ToolDocument> toolDocuments = toolDao.findByMadeInCountry("germany");
List<Long> carIds = toolDocuments.stream().map(tool -> tool.getUsedIn().stream().map(UsedIn::getCarId).collect(Collectors.toList())).flatMap(List::stream).collect(Collectors.toList());
List<CarDocument> carDocuments = carDao.findByMadeInCountryAndIdIn("germany", carIds);

UsedIn should contain carId as well as CarDocument Here are the questions which come up. What if carId is not kept? Why keep replicated data? — virsha, Mar 14 '17 at 17:04
Its what you need to do `$lookup`. Like I mentioned in my comment you cant use `DBRef`. So you need to store car reference id to do a lookup from tools. `CarDocument` to populate the values after aggregation. Dont worry about replicating data in mongodb. You should when you need for document database. — s7vr, Mar 14 '17 at 17:06
So it means that I have to replace `@DBRef` from the document. But I don't find this is good solution. Because car document can be stored in another shard of the DB. So DBRef has its own feature. https://docs.mongodb.com/manual/reference/database-references/ — virsha, Mar 14 '17 at 17:13
Anyways, I think your(@veeram) solution can solve my current problem for the timebeing but I don't find this is ideal solution. — virsha, Mar 14 '17 at 17:16
Yeah `lookup` wont work in sharding. I didn't know about sharding and I thought you wanted a lookup solution. I think ideal solution would be to do multiple calls and merge the information for each collections to get the desired output. If you do multiple calls, you don't need to store `CarDocument` in the `UsedIn` class. You can just store `carId` and try to look them up manually. I'll add that solution too. — s7vr, Mar 14 '17 at 17:24
I think, your update solution looks good, but can we have one DAO call, for two reasons, i. Want to return the documents in pagination ii. We can save space(List carIds can be million) then doing pagination is not perfect solution — virsha, Mar 15 '17 at 06:46
Some more questions. You are talking about paginating tool documents right ? how does your front-end look like ? B/c tool has many car relations. Do you have row for tool document and what/ how many car rows information do you show and how do you control that ? — s7vr, Mar 15 '17 at 11:38

score 0 · Answer 2 · answered May 13 '17 at 00:12

in general you should not make relations in MongoDB.

It may be tempting at first, if you come from relational databases, but in most cases duplication is a better idea in MongoDB.

if that does not sound like a good idea to you, maybe document databases are just not for you? :)

spring-data-mongodb: findAll() with list of input documents and search parameter for the embedded DBRef Document

2 Answers2