Recently, I received an email from a reader asking for tips on writing a MongoDB aggregation that aggregated the layers of a tree, stored in separate collections, into a single document:

Hi Pete,

I had a question related to your article on MongoDB object array lookup aggregations.

I’m working on something similar, but with a small difference. Imagine I have three collections that represent the different layers of a tree. A is the root. B are the children of A, and C are the children of B. Each child holds the ID of its parent in a parentId field.

The end goal is to write an aggregation that fleshes out every layer of the tree:

{
  _id
  B: [
    {
      _id
      parentId
      C: [
        {
          _id, 
          parentId
        }
      ]
    }
  ]
}

How should I approach this? Thanks.

Hello friend,

I feel your pain. Writing MongoDB aggregation feels like an under-documented dark art. In newer versions of Mongo you can write sub-pipelines under lookups. I think this will get you where you want to go:

db.getCollection('a').aggregate([
  {
    $lookup: {
      from: 'b',
      let: { "id": '$_id' },
      as: 'b',
      pipeline: [
        { $match: { $expr: { $eq: ['$$id', '$parentId'] } } },
        {
          $lookup: {
            from: 'c',
            let: { "id": '$_id' },
            as: 'c',
            pipeline: [
              { $match: { $expr: { $eq: ['$$id', '$parentId'] } } },
            ]
          }
        }
      ]
    }
  }
]);

You can keep adding sub-piplines until you get as deep as you need.

I hope that helps.

Pete