Help with collections $lookup

[Yoav_Melamed]

Hi,
I got the following collection with ‘customers’ documents. Each ‘customer’ document is embedded with ‘projects’ document array that have array of pointers to ‘projects.instances’:

  {
    id: 1,
    name: 'Customer Name',
    projects: [
      {
        name: 'Project 1',
        description: 'Project description',
        instances: [
          10
        ],
        
      },
      {
        name: 'Project 2',
        description: 'Project description',
        instances: [
          10,
          20
        ]
      }
    ]
  }

I have an additional collection with ‘instances’ documents as describe here:

{
  id: 10,
  operatingSystem: 'Microsoft Windows 2012R2',
  version: '3.1.5',
  product: {
    name: 'Product 1',
    vendor: 'Vendor A'
  }
},
{
  id: 20,
  operatingSystem: 'Microsoft Windows 2016',
  version: '4.1.0',
  product: {
    name: 'Product 2',
    vendor: 'Vendor B'
  }
}

My goal is to have a joined document for each customer that will look like this:

{
  id: 1,
  name: 'Customer Name',
  projects: [
    {
      name: 'Project 1',
      description: 'Project description',
      instances: [
        {
          id: 10,
          operatingSystem: 'Microsoft Windows 2012R2',
          version: '3.1.5',
          product: {
            name: 'Product 1',
            vendor: 'Vendor A'
          }
        }
      ]
    },
    {
      name: 'Project 2',
      description: 'Project description',
      instances: [
        {
          id: 10,
          operatingSystem: 'Microsoft Windows 2012R2',
          version: '3.1.5',
          product: {
            name: 'Product 1',
            vendor: 'Vendor A'
          }
        },
        {
          id: 20,
          operatingSystem: 'Microsoft Windows 2016',
          version: '4.1.0',
          product: {
            name: 'Product 2',
            vendor: 'Vendor B' 
          } 
        } 
      ]
    }
  ]
}

The results document in above is basically joins each matching instance to it ‘customer.projects.instances’ array

I struggling to achieve it
Could you please help me achieve my goal?

Thanks!

[slava]

Hello, @Yoav_Melamed! Welcome to the community!

You can join data from other collections by using $lookup stage of aggregation pipeline.

db.customers.aggregate([
  {
    $unwind: '$projects',
  },
  {
    $lookup: {
      from: 'instances',
      localField: 'projects.instances',
      foreignField: '_id',
      as: 'projects.instances',
    }
  },
  {
    $group: {
      _id: '$_id',
      projects: {
        $push: '$projects'
      }
    }
  }
]).pretty();