Help with data aggregation

I’ve got the following ‘customers’ collection with a customer document:

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

I’ve got an ‘instances’ collection with the following documents:

const Instances = [
  {
    id: 10,
    operatingSystem: 'Microsoft Windows 2012R2',
    version: '3.1.5',
    product: {
      id: 100,
      name: 'Product 1',
      vendor: 'Vendor A',
    },
  },
  {
    id: 20,
    operatingSystem: 'Microsoft Windows 2016',
    version: '4.1.0',
    product: {
      id: 200,
      name: 'Product 5',
      vendor: 'Vendor B',
    },
  },
  {
    id: 30,
    operatingSystem: 'Microsoft Windows 2019',
    version: '3.0',
    product: {
      id: 300,
      name: 'Product 2',
      vendor: 'Vendor A',
    },
  },
  {
    id: 40,
    operatingSystem: 'Linux',
    version: '1.0',
    product: {
      id: 100,
      name: 'Product 1',
      vendor: 'Vendor A',
    },
  },
];

After spending some hours, couldn’t find a way to aggregate my ‘customer details’ as expected:

const Results = {
  id: 1,
  name: 'Customer Name',
  projects: [
    {
      name: 'Project 1',
      description: 'Project description',
      products: [
        {
          id: 100,
          name: 'Product 1',
          vendor: 'Vendor A',
          instances: [
            {
              id: 10,
              operatingSystem: 'Microsoft Windows 2012R2',
              version: '3.1.5',
            },
            {
              id: 10,
              operatingSystem: 'Microsoft Windows 2012R2',
              version: '3.1.5',
            },
          ],
        },
        {
          id: 200,
          name: 'Product 5',
          vendor: 'Vendor B',
          instances: [
            {
              id: 20,
              operatingSystem: 'Microsoft Windows 2016',
              version: '4.1.0',
            },
          ],
        },
      ],
    },
    {
      name: 'Project 2',
      description: 'Project description',
      products: [
        {
          id: 300,
          name: 'Product 2',
          vendor: 'Vendor A',
          instances: {
            id: 30,
            operatingSystem: 'Microsoft Windows 2019',
            version: '3.0',
          },
        },
        {
          id: 100,
          name: 'Product 1',
          vendor: 'Vendor A',
          instances: [
            {
              id: 40,
              operatingSystem: 'Linux',
              version: '1.0',
            },
            {
              id: 10,
              operatingSystem: 'Microsoft Windows 2012R2',
              version: '3.1.5',
            },
          ],
        },
      ],
    },
  ],
};

Is it even possible?
Would appreciate your assistance

Everything is possible with MongoDB’s aggregation pipeline! Well, almost :laughing:

db.customers.aggregate([
  {
    $unwind: '$projects',
  },
  {
    $lookup: {
      from: 'instances',
      let: {
        ids: '$projects.instances',
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: ['$_id', '$$ids'],
            }
          }
        },
        {
          $group: {
            _id: '$product._id',
            name: {
              $first: '$product.name',
            },
            vendor: {
              $first: '$product.vendor',
            },
            instances: {
              $push: {
                _id: '$_id',
                operatingSystem: '$operatingSystem',
                version: '$version',
              }
            }
          }
        }
      ],
      as: 'projects.products',
    }
  },
  {
    $group: {
      _id: '$_id',
      name: {
        $first: '$name',
      },
      projects: {
        $push: '$projects',
      }
    }
  }
]).pretty();
2 Likes

Hi

You might even can cook coffee with it when come close enough to the CPUs and miss out the on or the other advice. :rofl:

Michael

3 Likes

It’s only possible when @slava around!
Thank you for your kind help :slight_smile:

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.