MongoDB multiple $lookup and $group output

Question

I'm quite a newbie with MongoDB and I'm trying to retrieve a kind-of leaderboard based on two related collections and a third one, referencing one of the two, based on its different property.

Schema can be found here

Consider a schema like the following one:

tree: { _id, company_id: string, company_name }
link: { _id, company_id: string, url: string }
analytics: { _id, tree_id: string, link_id: string, views: number, clicks: number, date: string }

A analytics document can have tree_id, views or link_id, clicks at once.
What I'm trying to achieve right now is a kind-of a "leaderboard" of the total clicks + views, starting from analytics collection, joining it with both tree and link, and finally retrieving the sum of clicks and views.
I have already managed to retrieve the sum of them for a specific company_id, with the following code

db.analytics.aggregate([{
    $lookup: {
        from: "trees",
        as: "trees",
        localField: "tree_id",
        foreignField: "_id"
    }
}, {
    $lookup: {
        from: "links",
        as: "links",
        localField: "link_id",
        foreignField: "_id"
    }
}, {
    $match: {
        $or: [
            {"trees.company_id": "1"},
            {"links.company_id": "1"}
        ]
    }
}, {
    $group: {
        _id: null,
        views_count: {
            $sum: "$views"
        },
        clicks_count: {
            $sum: "$clicks"
        }
    }
}])

But I can't find a way to get a list of results like

{ company_id: 1, company_name: "foo", clicks: 100, views: 200 },
{ company_id: 2, company_name: "bar", clicks: 200, views: 200 }

and so on.

What I've tried so far is grouping by different _id, which is not working as I would expect

db.analytics.aggregate([{
    $lookup: {
        from: "trees",
        as: "trees",
        localField: "tree_id",
        foreignField: "_id"
    }
}, {
    $lookup: {
        from: "links",
        as: "links",
        localField: "link_id",
        foreignField: "_id"
    }
}, {
    $group: {
        _id: "$trees.company_id",
        views_count: {
            $sum: "$views"
        },
        clicks_count: {
            $sum: "$clicks"
        }
    }
}])

Which does not assign clicks_count to a specific entry, but outputs something like

{ "_id" : [ "1" ], "views_count" : 6, "clicks_count" : 0 }
{ "_id" : [ ], "views_count" : 0, "clicks_count" : 48 }
{ "_id" : [ "2" ], "views_count" : 10, "clicks_count" : 0 }

I'm not even sure that this schema could be the best solution, so I will also appreciate any design suggestions or similar stuff.

Based on the comment below, I tried to deconstruct trees before grouping results, but it ended outputting the company_id, views_count only, without counting clicks, as following

{ "_id" : "2", "views_count" : 10, "clicks_count" : 0 }
{ "_id" : "1", "views_count" : 6, "clicks_count" : 0 }

user_8987128 · Answer

$addFields to add company field, check condition if trees.company_id not empty [] then return trees otherwise return links
$arrayElemAt to get first element from array
$group by company_id and sum your counts

db.analytics.aggregate([
  { $lookup: { //... } },
  { $lookup: { //... } },
  {
    $addFields: {
      company: {
        $arrayElemAt: [
          { $cond: [{ $ne: ["$trees.company_id", []] }, "$trees", "$links"] },
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: "$company.company_id",
      company_name: { $first: "$company.company_name" },
      views_count: { $sum: "$views" },
      clicks_count: { $sum: "$clicks" }
    }
  }
])

Playground

MongoDB multiple $lookup and $group output

1 Answers