Best Way to get count of Referenced Entities across collections

Working with Data
,
#1

Below are the three collections i am referencing in the topic

  1. Tags:
    {“id”:909,“name”:“newTag”}

  2. contacts
    {“id":1,“name”,“testcontact”,“email”:"test@test.com”,“tags”:[]}
    –tags field contaains list of References of linked tags collection document

  3. Campaign:
    {“id”:1,“name”,“testcampaign”,“tags”:[]}
    –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
[
{“id”:909,“name”:“newTag”,“campaignCount”:2,“contactCount”:5},
{“id”:909,“name”:“newTag”,“campaignCount”:0,“contactCount”:10}
]

#2

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.

#3

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

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

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

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

#5

@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?

#6

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

1 Like