Best Way to get count of Referenced Entities across collections

Below are the three collections i am referencing in the topic

  1. Tags:

  2. contacts
    –tags field contaains list of References of linked tags collection document

  3. Campaign:
    –tags field contaains list of References of linked tags collection document

Objective is to get list of all tags ,with the Count of Contacts & Campaigns who are referencing that particular tag.
I want to understand the best practice to do that through aggregation which is scalable and responds quickly as i am only interested in count of other collections.

An Example Result would be

Your expected result should match the supplied source documents.

We do not know how to generated 2 different counts from the same name:newTag.

We do not know how you refer to Tags from your tags:[] because you do not share what you have in the arrays. It could be id or name. But is it id or _id?

Please read Formatting code and log snippets in posts

and then update sample documents and expected results.

Hi Steeve,
I mistakenly typed same tag in expected results, and tried to edit,but there wasnt any option to edit.
Here is the Updated Sample Data and expected Result

  1. Tags
    "_id" : NumberLong(98),
    "name" : "testing"
  1. Contacts:
    "_id" : NumberLong(198),
    "email" : "",
    "firstname" : "Beta",
    "lastname" : "Jones",
    "extraFields" : {},
    "tags" : [ 
  1. Campaigns.

    "_id" : NumberLong(32),
    "title" : "test campaign",
    "description" : "test cam desc",
    "type" : "onetime",
    "status" : "created",
    "statusDesc" : "2/4 Steps",
    "tags" : [ 
  1. Expected Results:
    "_id" : NumberLong(98),
    "name" : "testing",

My approach would be

1 - $lookup stage from:Contacts localField:_id foreignField:tags as:contacts
2 - $set stage to replace contacts with its $size
3 - 1 and 2 for Campaings

@steevej i have achieved the Step 1 and Step 2 . However i am not sure how to pass /use the result from the first aggregation in the second. any suggestions?

nevermind figured it out. i had to do all steps in single agg query.

1 Like