Aggregate on two collections and get highest value

Question

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.

You can probably use the groupBy function describe in answer of this post : https://stackoverflow.com/questions/14446511/most-efficient-method-to-groupby-on-an-array-of-objects by Kmylo darkstar. Using a group by on username property and then loop + reduce to get the higher for each username — Nolyurn, Nov 22 '21 at 15:22

Takis · Accepted Answer · 2021-11-22T15:44:34.550

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]}}}])

Awesome answer. You answers are of quality without compromising on response time. Appreciate your dedication and knowledge! — hhharsha36, Nov 22 '21 at 16:07
thank you hhharsha, this answer was simple, i just tried to give both ways i could think of. — Takis, Nov 22 '21 at 16:37

Aggregate on two collections and get highest value

1 Answers1