How to group documents with unique ids and add the values of an array that also uses unique id'-CodePudding

I have the next data in my Mongo DB

{
  sucursal: 1
  productos: [
    {
      producto: 1,
      kilos: 50
    }
  ]
},
{
  sucursal: 2
  productos: [
    {
      producto: 1,
      kilos: 30
    },
    {
      producto: 2,
      kilos: 50
    }
  ]
},
{
  sucursal: 1
  productos: [
    {
      producto: 1,
      kilos: 50
    },
    {
      producto: 2,
      kilos: 40
    }
  ]
},

and my goal is to obtain the data with the following format

{
  sucursal: 1
  productos: [
    {
      producto: 1,
      kilos: 100
    },
    {
      producto: 2,
      kilos: 40
    }
  ]
},
{
  sucursal: 2
  productos: [
    {
      producto: 1,
      kilos: 30
    },
    {
      producto: 2,
      kilos: 50
    }
  ]
}

I am working with mongoose and nodeJS. I am using the aggregate function with $group but I'm not getting the expected results and I would like to know what I'm doing wrong. My goal is to group all 'sucursales' without repeating any elements, each 'sucursal' should have an array of all the 'productos' that are in each 'sucursal' with the same name but without repeating any 'producto', instead of repeating the element, the function should add the value to the element with the same name (see the data example above).

The code that I'm using is this:

const ordenesTotal = await OrdCompCli.aggregate([
      {
        $match: {fechaEntrega: new Date(fecha), estado: {$gt : 0, $lt : 5}}
      },
      {
        $unwind: "$productos"
      },
      {
        $group: {
          _id: "$sucursal",
          productos: {
            $addToSet: {
              producto: "$productos.producto",
              totalKilos: {
                $sum: "$productos.kilos"
              }
            }
          }
        }
      },
      {
        $project: {
          _id: 0,
          sucursal: "$_id",
          productos: 1
        },
      },
    ])

this code gave me this result:

{
  sucursal: 1
  productos: [
    {
      producto: 1,
      kilos: 50
    },
    {
      producto: 2,
      kilos: 40
    },
    {
      producto: 1,
      kilos: 50
    },
  ]
},
{
  sucursal: 2
  productos: [
    {
      producto: 1,
      kilos: 30
    },
    {
      producto: 2,
      kilos: 50
    }
  ]
}

and this result is not what I'm expecting because the 'producto' with the same name are not being grouped. Can someone tell me what I'm doing wrong?

CodePudding user response：

You need to do the $group in 2 separate stages.

db.collection.aggregate([
  {
    "$unwind": "$productos"
  },
  {
    $group: {
      _id: {
        sucursal: "$sucursal",
        producto: "$productos.producto"
      },
      kilos: {
        $sum: "$productos.kilos"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.sucursal",
      "productos": {
        "$push": {
          producto: "$_id.producto",
          kilos: "$kilos"
        }
      }
    }
  },
  //cosmetics
  {
    "$addFields": {
      "sucursal": "$_id"
    }
  },
  {
    "$unset": "_id"
  }
])

Mongo Playground

CodePudding user response：

This have been asked and answered many times before."group array of objects by a property". This one requires two levels of grouping though.

var input = [{
  sucursal: 1,
  productos: [{
    producto: 1,
    kilos: 50
  }]
}, {
  sucursal: 2,
  productos: [{
      producto: 1,
      kilos: 30
    },
    {
      producto: 2,
      kilos: 50
    }
  ]
}, {
  sucursal: 1,
  productos: [{
      producto: 1,
      kilos: 50
    },
    {
      producto: 2,
      kilos: 40
    }
  ]
}]

var output = Object.values(input.reduce(function(agg, item) {
  agg[item.sucursal] = agg[item.sucursal] || {
    sucursal: item.sucursal,
    productos: []
  }
  agg[item.sucursal].productos = [...agg[item.sucursal].productos, ...item.productos]
  return agg;
}, {})).map(function(item) {
  item.productos = Object.values(item.productos.reduce(function(agg, product) {
    agg[product.producto] = agg[product.producto] || {
      producto: product.producto,
      kilos: 0
    }
    agg[product.producto].kilos  = product.kilos
    return agg;
  }, {}))
  return item;
})

console.log(output)

.as-console-wrapper {
  max-height: 100% !important
}