How to use $lookup on array of subdocuments

Question

I have these Schemas:

const chatbots = new Schema({
  name: String,
  campaigns: [{
    name: String,
    channels: [{
      _id: String,
      name: String,
      budget: Number
    }]
  }]
});

const chatbotusers = new Schema({
  name: String,
  campaign_channel: String
})

And I need to get a list of Campaigns where, for each Channel, I have the total of ChatbotUsers. Something like this:

[
  {
    "name": "Campaign #1",
    "channels": {
      "_id": "eyRyZ1gD0",
      "name": "Channel #1",
      "users": 10
    }
  },
  {
    "name": "Campaign #1",
    "channels": {
      "_id": "tsKH7WxE",
      "name": "Channel #2",
      "users": 4
    }
  }
]

Any ideas?

The furthest I got was something like this:

{
  $lookup: {
    from: "chatbotusers",
    localField: "channels._id",
    foreignField: "campaign_channel",
    as: "users",
  }
},
{
  $project: {
    name: "$name",
    channels: {
      $map: {
        input: "$channels",
        as: "channel",
        in: {
          _id: "$$channel._id",
          name: "$$channel.name",
          users: { $size: "$users" },
        }
      }
    }
  }
}

But it sums the users for the Campaign, not the Channel.

(Sorry if the question title is not appropriate, I didn't even know how to ask this properly)

Answer 1

You can try this query :

db.chatbots.aggregate([
    {
        $lookup: {
            from: "chatbotusers",
            localField: "campaigns.channels._id",
            foreignField: "campaign_channel",
            as: "users"
        }
    },
    {
        $addFields: {
            campaigns: {
                $map: {
                    input: "$campaigns",
                    as: "eachCampaign",
                    in: {
                        $mergeObjects: ['$$eachCampaign', {
                            channels:
                            {
                                $reduce: {
                                    input: "$$eachCampaign.channels",
                                    initialValue: [],
                                    in: {
                                        $concatArrays: [
                                            "$$value",
                                            [
                                                {
                                                    $mergeObjects: [
                                                        "$$this",
                                                        {
                                                            user: {
                                                                $size: {
                                                                    $filter: {
                                                                        input: "$users",
                                                                        as: "e",
                                                                        cond: {
                                                                            $eq: [
                                                                                "$$e.campaign_channel",
                                                                                "$$this._id"
                                                                            ]
                                                                        }
                                                                    }
                                                                }
                                                            }
                                                        }
                                                    ]
                                                }
                                            ]
                                        ]
                                    }
                                }
                            }
                        }]
                    }
                }
            }
        }
    },
    {
        $project: {
            users: 0
        }
    }
])

Note : There can be multiple ways to do this, but this way we're working on same no.of docs from the chatbots collection rather than exploding docs by doing $unwind which may be helpful when you've huge dataset.

Test : MongoDB-Playground

This above query should get you what is needed, but in any case if it's slow or you think to enhance it then here :

{
    user: {
        $size: {
            $filter: {
                input: "$users", as: "e",
                    cond: {
                    $eq: [
                        "$$e.campaign_channel",
                        "$$this._id"
                    ]
                }
            }
        }
    }
}

Where We're iterating thru users array for every channel in every campaign, So instead of iterating every time, right after lookup - You can iterate over users for once using reduce to get count of each unique campaign_channel replace this data as users array, that way you can get count of users directly. In general main intention of above query is to preserve original document structure with less stages being used.

Alternatively you can use this query, which doesn't preserve original doc structure (also no.of docs in output can be more than what you've in collection) but can do what you needed :

db.chatbots.aggregate([
  {
    $unwind: "$campaigns"
  },
  {
    $unwind: "$campaigns.channels"
  },
  {
    $lookup: {
      from: "chatbotusers",
      localField: "campaigns.channels._id",
      foreignField: "campaign_channel",
      as: "users"
    }
  },
  {
    $addFields: {
      "channels": "$campaigns.channels",
      campaigns: "$campaigns.name"
    }
  },
  {
    $addFields: {
      "channels.users": {
        $size: "$users"
      }
    }
  },
  {
    $project: {
      users: 0
    }
  }
])

Test : MongoDB-Playground