This documentation page assumes that you already have a SeekTable account. You can create free account by signing up.

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:

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

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.

If you wasn't able to configure $lookup stage to join collections free to contact us and ask for assistance.