I would like to have the number of views of a post that has been viewed the most. Below is a small example.

Users:

{
   username: "John"
},
{
   username: "Doe"
}

Messages:

{
   title: "Lorem Ipsum!",
   views: 400,
   author: "John"
},
{
   title: "Lorem Ipsum!",
   views: 200,
   author: "John"
},
{
   title: "Lorem Ipsum!",
   views: 100,
   author: "John"
},
{
   title: "Lorem Ipsum!",
   views: 403,
   author: "Doe"
},
{
   title: "Lorem Ipsum!",
   views: 299,
   author: "Doe"
},

I want to have a property for each user on the object that contains the highest number of views of a message. Like this:

{
   username: "John"
   highest_view: 400
},
{
   username: "Doe"
   highest_view: 403
}

I could solve this in code by looping through and querying each object, but that doesn't seem like the most convenient way.

CodePudding user response：

Query1

• aggregate on users, lookup with Messages
• $max to keep the max value of the array from the path $highest_views.views

*$max is accumulator but also works on arrays

Test code here

users.aggregate(
[{"$lookup": 
    {"from": "Messages",
      "localField": "username",
      "foreignField": "author",
      "as": "highest_views"}},
 {"$set": {"highest_views": {"$max": "$highest_views.views"}}}])

Query2

• this is better, but a bit more complicated
• if you have big collections i think go for this one
• $lookup with pipeline, group to get the max views
• fix structure to get the expected output

Test code here

users.aggregate(
[{"$lookup": 
    {"from": "messages",
      "let": {"username": "$username"},
      "pipeline": 
      [{"$match": {"$expr": {"$eq": ["$$username", "$author"]}}},
        {"$group": {"_id": null, "highest_views": {"$max": "$views"}}}],
      "as": "highest_views"}},
  {"$set": 
    {"highest_views": 
      {"$arrayElemAt": ["$highest_views.highest_views", 0]}}}])