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.

To illustrate the approach 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. For this purpose we may add Custom Aggregate Pipeline Stages section in the cube configuration form:

  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"} }

    Notes:

    • projected fields from main collection are present in the "_id" document. That's why localField path should start with _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(): $lookup will not work if localField is string.

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