I am trying to aggregate an object with arrays. Would appreciate help.

here is my sample object.

    [       
       {
          "tipo": "A",
          "prices": [
              {
                  "min_pax": 1,
                  "max_pax": 3,
                  "type": "One Way",
                  "price": 35
              },
              {
                  "min_pax": 1,
                  "max_pax": 3,
                  "type": "Round Trip",
                  "price": 63
              },
              {
                  "min_pax": 4,
                  "max_pax": 6,
                  "type": "One Way",
                  "price": 40
              },
              {
                  "min_pax": 4,
                  "max_pax": 6,
                  "type": "Round Trip",
                  "price": 65
              },
              {
                  "min_pax": 7,
                  "max_pax": 10,
                  "type": "One Way",
                  "price": 50
              },
              {
                  "min_pax": 7,
                  "max_pax": 10,
                  "type": "Round Trip",
                  "price": 80
              }
          ],
       }
   ]

I want to merge objects with same number of min & max pax, for example:

Between 1 and 3 passengers for one way trip the cost is 35, for round trip the cost is 63.

I think looks better is I can merge both objects.

I would like something like this:

[
   {
       "tipo": "A",
       "prices": [
           {
               "min_pax": 1,
               "max_pax": 3,
               "one_way": 35,
               "round_trip": 63
           },
           {
               "min_pax": 4,
               "max_pax": 6,
               "one_way": 40,
               "round_trip":65
           },
           {
               "min_pax": 7,
               "max_pax": 10,
               "one_way": 50,
               "round_trip": 80
           },
       ],
   }
]

I would really appreciate your help

CodePudding user response：

One option is to $unwind and $group again according to the pax:

db.collection.aggregate([
  {$unwind: "$prices"},
  {$group: {
      _id: {max_pax: "$prices.max_pax", min_pax: "$prices.min_pax"},
      tipo: {$first: "$tipo"},
      data: {$push: {k: "$prices.type", v: "$prices.price"}}
    }
  },
  {$project: {
      _id: 0,
      tipo: 1,
      max_pax: "$_id.max_pax",
      min_pax: "$_id.min_pax",
      data: {$arrayToObject: "$data"}
    }
  },
  {$set: {"data.max_pax": "$max_pax", "data.min_pax": "$min_pax"}},
  {$group: {_id: "$tipo", prices: {$push: "$data"}}},
  {$project: {_id: 0, tipo: "$_id", prices: 1}}
])

See how it works on the playground example