MongoDB Unwind In Parallel Stages or Combine

Question

Given the following data:

let tasks = [
  {
    _id: 1,
    task_number: 1,
    description: 'Clean Bathroom',
    customer: 'Walmart',
    users_worked: [
      {user: 'Jonny', hours: 1},
      {user: 'Cindy', hours: 1}
    ],
    supplies_used: [
      {item_code: 'LD4949', description: 'Liquid Detergent', quantity: 1}
    ]
  },
  {
    _id: 2,
    task_number: 2,
    description: 'Stock Cheeses',
    customer: 'Walmart',
    users_worked: [
      {user: 'Mark', hours: 3.0},
      {user: 'Shelby', hours: 2.0}
    ],
    supplies_used: []
  }
];

Suppose I want to show a table with each one of these in a list format:

task_number | description | customer | users | users_worked.hours (sum) | supplies_used.quantity (sum)
----------------------------------------------------------------------------------------------
      1 | 'Clean Bathroom' | 'Walmart' | 'Jonny, Cindy' |        2                |           1
      2 | 'Stock Cheeses'  | 'Walmart' | 'Mark, Shelby' |        5                |           0

The aggregate:

[
  {
    "unwind: {
      "path": "$users_worked",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    "unwind: {
      "path": "$supplies_used",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $group: {
      _id: "$_id",
      task_number: {
        $first: "$task_number"
      },
      description: {
        $first: "$description"
      },
      customer: {
        $first: "$customer"
      },
      users: {
        $push: "$users_worked.user"
      },
      users_worked: {
        $sum: "$users_worked.hours"
      },
      supplies_used: {
        $sum: "$supplies_used.quantity"
      }
  }
]

The problem is that I need to $unwind both arrays (users_worked and supplies_used), which ends up skewing my results (cartesian product). Since task #1 has 2 elements in users_worked array, it will make my supplies_used count go to 2.

This is a simple example, there could be many arrays and the more elements each have the more it skews the data.

Is there a way with aggregates to unwind multiple arrays separately so they don't skew each other? I have seen an example of creating 1 combined object, where then theres only 1 source of unwinding. Don't seem to understand how to do what I want though.

* EDIT *

I see that you can use the $zip mongo aggregate command to combine multiple arrays into 1 array. This is a good start:

arrays: {
  $map: {
    input: {
      $zip: {
        inputs: [
          '$users_worked',
          '$supplies_used'
        ], 
      }
    },
    as: 'zipped',
    in: {
      users_worked: {
        $arrayElemAt: [
          '$$zipped',
          0
        ]
      },
      supplies_used: {
        $arrayElemAt: [
          '$$zipped',
          1
        ]
      }

How can I use this $zip command if I were to have an array of array. For example:

let tasks = [
  {
    _id: 1,
    task_number: 1,
    description: 'Clean Bathroom',
    customer: 'Walmart',
    users_worked: [
      {user: 'Jonny', hours: 1},
      {user: 'Cindy', hours: 1}
    ],
    supplies_used: [
      {item_code: 'LD4949', description: 'Liquid Detergent', quantity: 1}
    ],
    invoices: [
      {
        invoicable: true,
        items: [
           {item_code: 'LD4949', price: 39.99, quantity: 1, total: 39.99},
           {item_code: 'Hours', price: 50.00, quantity: 2, total: 100.00}
        ]
      }
    ]
  },
  {
    _id: 2,
    task_number: 2,
    description: 'Stock Cheeses',
    customer: 'Walmart',
    users_worked: [
      {user: 'Mark', hours: 3.0},
      {user: 'Shelby', hours: 2.0}
    ],
    supplies_used: [],
    invoices: []
  }
];

And I want to include the sum of invoices.items.total in my list.

Answer 1

I was able to solve this using multiple layers of $zip. Starts on root layer, all arrays on root need to be zipped and then unwound. Then you find the $sum of any fields on that layer, then you find next layer, $zip new arrays, then unwind, then sum that layer, etc.

The issue is you don't want to unwind multiple arrays or you get the cartesian product. Need to combine into 1 array then unwind then everything is correct!

Answer 2

Instead of using $unwind and $group you can use $reduce to aggregate names and $sum to sum up the numbers:

db.collection.aggregate([
    {
        $project: {
            task_number: 1,
            description: 1,
            customer: 1,
            users: {
                $reduce: {
                    input: "$users_worked",
                    initialValue: "",
                    in: {
                        $concat: [ "$$value", ", ", "$$this.user" ]
                    }
                }
            },
            users_worked: { $sum: "$users_worked.hours" },
            quantity: { $sum: "$supplies_used.quantity" }
        }
    }
])

Mongo Playground