Compare and add two array inside nested arrays

Question

I have below array of array inside my document

  {
    items: [
      ["Tax", 10, 20, 30],
      ["FX Adjustments", 10, 20, 30],
      ["Tax", 10, 20, 30],
      ["FX Adjustments", 10, 20, 30]
    ]
  }

I need to combine Tax with Tax and FX Adjustments with FX Adjustments.

Output I need

  {
    items: [
      ["Tax", 20, 40, 60],
      ["FX Adjustments", 20, 40, 60]
    ]
  }

I tried but with no luck

db.collection.aggregate([
  {
    $project: {
      items: {
        $reduce: {
          input: "$items",
          initialValue: [],
          in: {
            $cond: [
            ]
          }
        }
      }
    }
  }
])

Kindly help with this

Thank you!!!

mickl · Accepted Answer · 2019-11-26 22:03:11Z

1

You need to start with $unwind and then $group by first array element ($arrayElemAt). Then you can run $reduce like you tried but since your array is multidimensional, you need to wrap it with $map to represent the index. To get back the original data format you can group by null and use $concatArrays to put grouping _id as first array element:

db.collection.aggregate([
    {
        $unwind: "$items"
    },
    {
        $group: {
            _id: { $arrayElemAt: [ "$items", 0 ] },
            values: { $push: { $slice: [ "$items", 1, { $size: "$items" } ] } }
        }
    },
    {
        $project: {
            _id: 1,
            values: {
                $map: {
                    input: { $range: [ 0, { $size: { $arrayElemAt: [ "$values", 0 ] } } ] },
                    as: "index",
                    in: {
                        $reduce: {
                            input: "$values",
                            initialValue: 0,
                            in: { $add: [ "$$value", { $arrayElemAt: [ "$$this", "$$index" ] } ] }
                        }
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            items: { $push: { $concatArrays: [ [ "$_id" ], "$values" ] } }
        }
    }
])

Mongo Playground

answered Nov 26, 2019 at 22:03

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Profer Over a year ago

Can It be possible without $unwind... Using $reduce?

mickl Over a year ago

@Profer theoretically yes, but the code would look kess readable IMO - you can try with $let and $arrayElemAt

Profer Over a year ago

Thank you but if you can show me that way... I can easily understand it... BTW what is IMO?

mickl Over a year ago

IMO = in my opinion

sushant mehta · Accepted Answer · 2019-11-26 07:23:24Z

Try below aggregate pipeline

collectionName.aggregate([
  {
    $unwind: "$items"
  },
  {
    $group: {
      _id: {
        $arrayElemAt: [
          "$items",
          0
        ]
      },
      sum1: {
        $sum: {
          $arrayElemAt: [
            "$items",
            1
          ]
        }
      },
      sum2: {
        $sum: {
          $arrayElemAt: [
            "$items",
            2
          ]
        }
      },
      sum3: {
        $sum: {
          $arrayElemAt: [
            "$items",
            3
          ]
        }
      }
    }
  },
  {
    $project: {
      _id: null,
      items: {
        $map: {
          input: {
            $objectToArray: "$$ROOT"
          },
          as: "item",
          in: "$$item.v"
        }
      }
    }
  },
  {
    $group: {
      _id: null,
      items: {
        $push: "$items"
      }
    }
  }
])

unwind the items array
group according to Tax(first position element of item using $arrayElemAt)
In project stage use map on objectToArray to get value of keys pushed into array
push the array using $group giving desired output

Output:

[
  {
    "_id": null,
    "items": [
      [
        "Tax",
        20,
        40,
        60
      ],
      [
        "FX Adjustments",
        20,
        40,
        60
      ]
    ]
  }
]

Collectives™ on Stack Overflow

Compare and add two array inside nested arrays

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related