How to implement aggregation query in Spring Data MongoDB?

Question

I am new with Spring Data MongoDB and I am trying to implement an aggregation query in Java with Spring Data MongoDB. I have tried searching from this problem and approached it using MongoTemplate, but still to no result.

The format of my data:

[{ 
    "_id" : ObjectId("5e1aea6c275360baf96bac29"), 
    "title" : "postim", 
    "upvotesBy" : [
        "5e18b4c12753608718dfa007", 
        "5e19ac0f5161a4994ded1f35"
    ], 
    "file" : "test", 
    "description" : "description", 
    "postedBy" : "5e18b4c12753608718dfa007", 
    "createdAt" : ISODate("2020-01-12T09:44:12.119+0000"), 
    "_class" : "com.socialnetwork.post.Post"
},
{ 
    "_id" : ObjectId("5e1aeaf8275360bb4bb47325"), 
    "title" : "postim2", 
    "upvotesBy" : [
        "5e18b4c12753608718dfa007", 
        "5e19ac0f5161a4994ded1f35"
    ], 
    "file" : "test2", 
    "description" : "description2", 
    "postedBy" : "5e18b4c12753608718dfa007", 
    "createdAt" : ISODate("2020-01-12T09:46:32.909+0000"), 
    "_class" : "com.socialnetwork.post.Post"
}]

My query:

db.post.aggregate([
    {
      $match: {}
    },
    {
      $lookup: {
        from: "users",
        localField: "postedBy",
        foreignField: "_id",
        as: "user"
      }
    },
    {
      $group: {
        _id: {
          username: "$user.name",
          title: "$title",
          description: "$description",
          upvotes: { $size: "$upvotesBy" },
          upvotesBy: "$upvotesBy",
          isUpvoted: { $in: [req.query.userId, "$upvotesBy"] },
          isPinned: {
            $cond: {
              if: { $gte: [{ $size: "$upvotesBy" }, 3] },
              then: true,
              else: false
            }
          },
          file: "$file",
          createdAt: {
            $dateToString: {
              format: "%H:%M %d-%m-%Y",
              timezone: "+01",
              date: "$createdAt"
            }
          },
          id: "$_id"
        }
      }
    },
    { $sort: { "_id.isPinned": -1, "_id.createdAt": -1 } }
])

This is the query I use in my Javascript backend and I can do this fairly easy with Mongoose. However I am having some difficulty with the Java implementation of it.

private LookupOperation getLookupOperation() {
        return LookupOperation.newLookup().from("user")
                .localField("postedBy")
                .foreignField("_id")
                .as("user");
    }

    @Override
    public List<PostSummary> aggregate() {
        LookupOperation lookupOperation = getLookupOperation();
        return mongoTemplate.aggregate(Aggregation.newAggregation(lookupOperation, Aggregation.group("id")
                .addToSet("user.name").as("username")
                .addToSet("title").as("title")
                .addToSet("description").as("description")
                .addToSet("id").as("id")
                .push("upvotesBy").as("upvotesBy")
                .addToSet("file").as("file")
                .addToSet("createdAt").as("createdAt")
        ), Post.class, PostSummary.class).getMappedResults();
}

When I try to run this I get the following error:

"Cannot convert [] of type class java.util.ArrayList into an instance of class java.lang.Object! Implement a custom Converter<class java.util.ArrayList, class java.lang.Object> and register it with the CustomConversions. Parent object was: com.socialnetwork.post.PostSummary@7159d908"

When I delete the .addToSet("user.name").as("username") from the group aggregation I also get an error from .push("upvotesBy").as("upvotesBy") as it can not convert [] of type class java.util.ArrayList into an instance of class java.lang.String

Also the implementation of the Post Class and the PostSummary Class is simple:

Post.java:

@Document
public class Post {
    @Id
    private String id;
    private String title;
    private List<String> upvotesBy;
    private String file;
    private String description;
    private String postedBy;
    private Date createdAt = new Date();

//  ... Getters and Setters for each field
}

PostSummary.java:

public class PostSummary {
    private String username;
    private String title;
    private String description;
    private List<String> upvotesBy;
    private String file;
    private String createdAt;
    private String id;

//... Getters and Setters for the class
}

I also need to implement the isUpvoted and isPinned part of the query, but getting the idea on how to approach the first problem would be a great start.

EDIT: My desired output:

[
{
   "username" : "user1", 
   "title" : "postim2", 
   "upvotesBy" : [
      "5e18b4c12753608718dfa007", 
      "5e19ac0f5161a4994ded1f35"
   ],
   "file": "file1",
   id: "5e18b4c12753608718dber01"
   ... Other fields of the original post
},
{
   "username" : "user2", 
   "title" : "postim2", 
   "upvotesBy" : [
      "5e18b4c12753608718dfa007", 
      "5e19ac0f5161a4994ded1f35"
   ],
   id: "5e18b4c12753608718dber02",
   "file": "file2",
   ... Other fields of the original post
}
]

So from the lookup operation I need only to get the name of the user.

The problem is that $lookup returns Array, not single user document. So, your aggregation fails in $group stage (username: "$user.name"). If you add {"$unwind":"user"} before $group stage, it will work — Valijon
– Valijon, Commented Jan 12, 2020 at 11:34
@Valijon my attempt with the public List<PostSummary> aggregate() is the Spring-Data version, right? — David Prifti
– David Prifti, Commented Jan 12, 2020 at 11:36
Are you developing as maven project? Post here pom.xml file — Valijon
– Valijon, Commented Jan 12, 2020 at 11:44

Valijon · Accepted Answer · 2020-01-12 15:21:45Z

Let's do it

We need to update your aggregation to make it work.

Errors:

users's _id is ObjectId type, but in your post you have stored as String, so $lookup should be changed to Uncorrelated sub-queries
We replace $group by '$addFields' which fits better
We add as last stage $project operator to exclude all unsed fields.

db.post.aggregate([
  {
    $match: {}
  },
  {
    $lookup: {
      from: "users",
      let: {
        postedBy: "$postedBy"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                {
                  "$toString": "$_id"
                },
                "$$postedBy"
              ]
            }
          }
        }
      ],
      as: "user"
    }
  },
  {
    $unwind: "$user"
  },
  {
    $addFields: {
      id: {
        $toString: "$_id"
      },
      username: "$user.name",
      upvotes: {
        $size: "$upvotesBy"
      },
      isUpvoted: {
        $in: [
          "5e18b4c12753608718dfa007",
          "$upvotesBy"
        ]
      },
      isPinned: {
        $cond: [
          {
            $gte: [
              {
                $size: "$upvotesBy"
              },
              3
            ]
          },
          true,
          false
        ]
      },
      createdAt: {
        $dateToString: {
          format: "%H:%M %d-%m-%Y",
          timezone: "+01",
          date: "$createdAt"
        }
      }
    }
  },
  {
    $sort: {
      "isPinned": -1,
      "createdAt": -1
    }
  },
  {
    $project: {
      _id: 0,
      user: 0,
      upvotesBy: 0,
      _class: 0
    }
  }
])

Now, we transform this query to Spring-Data syntax.

Java Implementation

package postman;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.match;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.project;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.sort;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.unwind;

import java.util.Arrays;
import java.util.List;

import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperationContext;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

@Service
public class PostmanService {

    @Autowired
    private MongoTemplate mongoTemplate;

    public List<PostSummary> find(String userId){

        Aggregation aggregation = Aggregation.newAggregation(
            match(new Criteria()),
            //lookup("users", "postedBy", "_id", "user")
            new AggregationOperation() {
                @Override
                public Document toDocument(AggregationOperationContext context) {
                    return new Document("$lookup",
                        new Document("from", "users")
                            .append("let", new Document("postedBy", "$postedBy"))
                            .append("pipeline", Arrays.asList(
                                new Document("$match", 
                                    new Document("$expr", 
                                        new Document("$eq", Arrays.asList(
                                            new Document("$toString", "$_id"),
                                            "$$postedBy"
                                        ))))))
                            .append("as", "user"));
                }
            },
            unwind("$user"),
            new AggregationOperation() {

                @Override
                public Document toDocument(AggregationOperationContext context) {
                    return new Document("$addFields",
                        new Document("id", new Document("$toString", "$_id"))
                        .append("username", "$user.name")
                        .append("upvotes", new Document("$size", "$upvotesBy"))
                        .append("isUpvoted", new Document("$in", Arrays.asList(userId, "$upvotesBy")))
                        .append("isPinned", new Document("$cond", 
                            Arrays.asList(new Document("$gte", 
                                    Arrays.asList(new Document("$size", "$upvotesBy"), 3)), Boolean.TRUE, Boolean.FALSE)))
                        .append("createdAt", new Document("$dateToString", 
                            new Document("format", "%H:%M %d-%m-%Y")
                                .append("timezone", "+01")
                                .append("date", "$createdAt")
                            )));
                }
            },
            sort(Direction.DESC, "isPinned", "createdAt"),
            project().andExclude("user", "_class")
        );

        System.out.println("Aggregation: " + aggregation.toString());

        return mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(Post.class), PostSummary.class).getMappedResults();
    }
}

Now, we call aggregation pipeline:

List<PostSummary> l = postmanService.find("5e18b4c12753608718dfa007");
for(PostSummary post: l) {
    ObjectWriter ow = new ObjectMapper().writer().withDefaultPrettyPrinter();
    System.out.println(ow.writeValueAsString(post));
}

2020-01-12 16:15:22.043  INFO 11148 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-01-12 16:15:22.047  INFO 11148 --- [           main] Postman.PostmanApplication               : Started PostmanApplication in 4.602 seconds (JVM running for 5.301)
Aggregation: { "aggregate" : "__collection__", "pipeline" : [{ "$match" : {}}, { "$lookup" : { "from" : "users", "let" : { "postedBy" : "$postedBy"}, "pipeline" : [{ "$match" : { "$expr" : { "$eq" : [{ "$toString" : "$_id"}, "$$postedBy"]}}}], "as" : "user"}}, { "$unwind" : "$user"}, { "$addFields" : { "id" : { "$toString" : "$_id"}, "username" : "$user.name", "upvotes" : { "$size" : "$upvotesBy"}, "isUpvoted" : { "$in" : ["5e18b4c12753608718dfa007", "$upvotesBy"]}, "isPinned" : { "$cond" : [{ "$gte" : [{ "$size" : "$upvotesBy"}, 3]}, true, false]}, "createdAt" : { "$dateToString" : { "format" : "%H:%M %d-%m-%Y", "timezone" : "+01", "date" : "$createdAt"}}}}, { "$sort" : { "isPinned" : -1, "createdAt" : -1}}, { "$project" : { "user" : 0, "_class" : 0}}]}
2020-01-12 16:15:22.161  INFO 11148 --- [           main] org.mongodb.driver.connection            : Opened connection [connectionId{localValue:2, serverValue:277}] to localhost:27017
{
  "username" : "user1",
  "title" : "postim2",
  "description" : "description2",
  "upvotesBy" : [ "5e18b4c12753608718dfa007", "5e19ac0f5161a4994ded1f35" ],
  "file" : "test2",
  "createdAt" : "10:46 12-01-2020",
  "id" : "5e1aeaf8275360bb4bb47325"
}
{
  "username" : "user1",
  "title" : "postim",
  "description" : "description",
  "upvotesBy" : [ "5e18b4c12753608718dfa007", "5e19ac0f5161a4994ded1f35" ],
  "file" : "test",
  "createdAt" : "10:44 12-01-2020",
  "id" : "5e1aea6c275360baf96bac29"
}

I also updated the postedBy field so that now it holds an ObjectId reference and not a String. So the lookUp operation can be done as I set it initially.
I will continue assuming as Sting, then you can adap it as your needs

Collectives™ on Stack Overflow

How to implement aggregation query in Spring Data MongoDB?

1 Answer 1

Let's do it

Java Implementation

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Let's do it

Java Implementation

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related