MongoDb Lookup how to join multiple collections

If 'main' cube collection has documents with a field that refers to ID of another collection (foreign key) you may configure $lookup stage to resolve fields from this another collection. Depending on relationship cardinality you may configure $lookup stage in one of the two ways:

• apply $lookup before $group (used in case of high-cardinality)
• apply $lookup after $group (used in case of low-cardinality for more efficiency)

To illustrate both approaches let's assume that we have 2 collections:

• users with field company_id
• companies with _id and company_name fields

Cube uses users as main collection and we want to display company_name instead of company_id. To configure $lookup use Custom Aggregate Pipeline Stages section on the cube configuration form.

$lookup BEFORE $group

1. Add $lookup stage:
   • Click on "+ Custom Stage"
   • Set Execute to Before $group
   • Set Custom Stage JSON to:

     {$lookup: {from:"companies", localField:"company_id", foreignField:"_id", as:"Company"} }

2. Add $unwind stage (it is needed because $lookup always return an array):
   • Click on "+ Custom Stage"
   • Set Execute to Before $group
   • set Custom Stage JSON to:

     {"$unwind": {"path":"$Company", "preserveNullAndEmptyArrays": true } }

3. Add dimension for company name:
   • Click on "+ Dimension"
   • set Name to Company.company_name (path to sub-document field after unwind)

You can apply custom $lookup + $unwind stages only when report has dimensions that use 'joined' data; for this purpose you can list them in the Apply for Fields.

$lookup AFTER $group

1. Add $lookup stage:
   • Click on "+ Custom Stage"
   • Set Execute to After $group
   • Set Custom Stage JSON to:

     {$lookup: {from:"companies", localField:"_id.company_id", foreignField:"_id", as:"Company"} }

   Important notes:
   • projected fields from main collection are present in the "_id" document. That's why localField path should start with _id.
   • if localField refers to a field in a sub-object replace "." with "__", for example: company.id → _id.company__id
   • $lookup always returns array even if only one document is matched. To use "Company" as sub-document $unwind stage should be specified.
2. Add $unwind stage:
   • Click on "+ Custom Stage"
   • Set Execute to After $group
   • set Custom Stage JSON to:

     {"$unwind": {"path":"$Company", "preserveNullAndEmptyArrays": true } }

3. Add dimension for company name:
   • Click on "+ Dimension"
   • set Name to Company.company_name (path to sub-document field after unwind)
   • add one Parameter with value company_id (name of the field that is foreign key in the main collection)

If you get company_id values for Company.company_name dimension ensure that $lookup is configured correctly.
NOTE: both localField and foreignField should have the same type. If foreignField is objectId() then localField values also should be objectId(); if localField is a string then foreignField should have string type too.