My main schema sort of looks like this:

 _id: id,
 random: random, 
 cards: [objectId, objectId, ...]   //ref to cards

ex of card:

 _id: id,
 random: random, 
 random: random,
 clicks: 15.

I would like to sort the top schema by the sum of clicks within the cards.

CodePudding user response：

Query1

• lookup to do the left join, and joined card documents to be an array (join happens if array contains the card _id)
• sum the clicks
• sort by the field that has the sum

*if you want descending use sort -1 instead of 1

Test code here

mainColl.aggregate(
[{"$lookup": 
   {"from": "cardCollection",
    "localField": "cards",
    "foreignField": "_id", 
    "as": "clickSum"}},
 {"$set": {"clickSum": {"$sum": "$clickSum.clicks"}}},
 {"$sort": {"clickSum": 1}}])

Query2

• same like the above but does the calculation before the join (its better but slightly bigger code)

Test code here

mainColl.aggregate(
[{"$lookup": 
    {"from": "cardsCollection",  
      "localField": "cards",
      "foreignField": "_id",
      "pipeline": 
      [{"$group": {"_id": null, "clicksSum": {"$sum": "$clicks"}}}],
      "as": "clicksSum"}},
  {"$set": {"clicksSum": {"$arrayElemAt": ["$clicksSum.clicksSum", 0]}}},
  {"$sort": {"clickSum": 1}}])

CodePudding user response：

I ended up using this:

model.aggregate([ 
          {$lookup: {from: "cards", localField: "cards", foreignField: "_id", as: "cards"}},
          {$set: {clickSum: {$sum: "$cards.clicks"}}},
          {$sort: {clickSum: -1}}
            ])