How to create complex custom query with mongo template

Question

Im using Java 8 and Spring, trying to create a query with mongo template.

I have this 3 collections:

public class User {     
    private String id;     
    private String catalogId;  
    private String name;  
    private String info;  
} 
 
public class Catalog {     
    private String id;     
    private String userId;     
    private boolean exist; 
} 

public class Item {     
    private String id;    
    private String catalogId;    
    private String userId; 
} 

I want to create a query that returns the following:

class Response {
  private User user;
  private int countOfCatalogs;
  private int countOfItems;
}

The idea is to return a list with that information for each user and the count of catalogs and items for each user, a List<Response. With pagination and sorting on ‘countOfCatalogs’ and ‘countOfItems’.

Is there a way to do something like this with Mongo template?

Thanks!

Answer 1

First of all: Yes, you can do that using aggregation pipeline BUT... are you sure you need a NoSQL DB to perform two joins? It seems your data is highly related.

By the way, this is only a suggestion. Answering your question I think you want this query

Which can be translated into Java code like this (not tested at all):

Aggregation aggregation = Aggregation.newAggregation(
    Aggregation.lookup("Catalog", "_id", "userId", "catalogs"),
    Aggregation.lookup("Item", "_id", "userId", "items"),
    Aggregation.project()
        .andExclude("_id")
        .and("_id").as("user._id")
        .and("catalogId").as("user.catalogId")
        .and("name").as("user.name")
        .and("info").as("user.info")
        .and("catalogs"),size().as("countOfCatalogs")
        .and("items"),size().as("countOfItems")
);

AggregationResults <Response> results = mongoTemplate.aggregate(aggregation, "User", Response.class);