CODEWITHSUNDEEP
mongodbmeteoraggregation-framework
pietro abate
pietro abate

Reputation: 479

sort mongo collection based on the score of child documents

I've two collections, editors and books. Each book is associated with a parentId field to an editor and each book is has a score ( say 1, 2, 3 ) and a type ( sci-fi, romance, etc ...) .

Editors:

{ _id: 1, name: "editor1" }
{ _id: 2, name: "editor2" }
...

and the books

{ _id: 1, name: "book1", score: 1, parentId: 1, type: "sci-fi" }
{ _id: 2, name: "book2", score: 3, parentId: 1, type: "romance" }

{ _id: n, name: "bookn", score: 1, parentId: m, type: "detective" }

I want to write an aggregation that will sort the editors based on the score of all books associated to it, and optionally by type of the books.

So i can retrieve the first 10 editors of sci-fi with the most popular books, or just the first 10 editors with the most popular books regardless of categories.

The catch ? Using mongo 3.2 . I've the strong impression that this is possible with 3.4 and 3.6 (And I'd love to see how), but at the moment, the product I'm shipping is with mongo 3.2 and I can't change that ...

Aggregating on the editors collection, I've tried to first lookup all the books for an editor, then unwind, group by parentId and create a new field score with the sum of the score of all books in this group, but then I'm stuck trying to use this score to associate it to the editors and finally sort the result.

I'm using this aggregation in a meteor subscription.

Upvotes: 0

Views: 167

Answers (2)

s7vr
s7vr

Reputation: 75934

You can try below aggregation.

So i can retrieve the first 10 editors of sci-fi with the most popular books

db.editors.aggregate([
  {"$lookup":{
    "from":"books",
    "localField":"_id",
    "foreignField":"parentId",
    "as":"books"
  }},
  {"$unwind":"$books"},
  {"$match":{"books.type":"sci-fi"}},
  {"$group":{
    "_id":"$_id",
    "name":{"$first":"$name"},
    "scores":{"$sum":"$books.score"}
  }},
  {"$sort":{"scores":-1}},
  {"$limit":10}
])

or just the first 10 editors with the most popular books regardless of categories.

db.editors.aggregate([
  {"$lookup":{
    "from":"books",
    "localField":"_id",
    "foreignField":"parentId",
    "as":"books"
  }},
  {"$project":{
    "name":1,
    "scores":{"$sum":"$books.score"}
  }},
  {"$sort":{"scores":-1}}
])

Upvotes: 2

mickl
mickl

Reputation: 49945

Try following aggregation:

db.Editors.aggregate([
    {
        $lookup: {
            from: "Books",
            localField: "_id",
            foreignField: "parentId",
            as: "books"
        }
    },
    {
        $unwind: "$books"
    },
    {
        $group: {
            _id: "_id",
            name: { $first: "$name" },
            types: { $addToSet: "$books.type" },
            totalScore: { $sum: "$books.score" }
        }
    },
    {
        $match: { "types": "sci-fi" }
    },
    {
        $sort: { "totalScore": -1 }
    }
])

One catch with $match stage. You can use it before $group to calculate scores only for sci-fi books or after $group to calculate score for all books but then get only those authors having at least one sci-fi book (as in my answer)

Upvotes: 1

Related Questions