How to use $subtract along with conditions to use a default value from an array, if null?

Question

I'm trying to subtract values from final array and start array in my aggregation pipeline. But there are certain exceptional cases that needs some additional logic before subtraction.

Expected Output:

I need to subtract nth value of startarray from nth value of final array

And then, get the total sum of subtracted values

Exceptional Cases:

If nth value of start array is NULL, use a start_default value(from the query)

If nth value of final array is NULL, use value from the final_default array

After some of the aggregation stages, my MongoDB document has this format:

Assuming that start_default value = 1

I have commented the way I expect to perform subtractions in each of the group

{
  "data": [
    {
      "key": "TP-1",
      "status_map": [
        {
          "status": "Closed",
          "final": [
            6,
            3
          ],                               // Expected Output
          "start": [                       // sum:6 [(6-2)+(3-1(start_default))=4+2]
            2
          ],
          "final_default": [
            4
          ]
        },
        {
          "status": "Done",
          "final": [
            4
          ],                              // Expected Output
          "start": [                     // sum:2 [(4-3)+(2(final_default)-1)=1+1]
            3,
            1                            
          ],
          "final_default": [
            2
          ]
        }
      ]
    },
    {
      "key": "TP-2",
      "status_map": [
        {
          "status": "Closed",
          "final": [
            1,
            5
          ],                             // Expected Output
          "start": [],                   //sum:4 [(1-1(start_default))+(5-1(start_default))=0+4]
          "final_default": [
            3
          ]
        },
        {
          "status": "Done",
          "final": [],                    // Expected Output
          "start": [                     //sum:3 [(5(final_default)-3)+(5(final_default)-4)=2+1]
            3,
            4
          ],
          "final_default": [
            5
          ]
        }
      ]
    }
  ]
}

Here is my expected output assuming that start_default value = 1

{
  "data": [
    {
      "key": "TP-1",
      "status_map": [
        {
          "status": "Closed",
          "sum": 6  //[(6-2)+(3-1(start_default))=4+2]
        {
          "status": "Done",
          "sum": 2 //[(4-3)+(2(final_default)-1)=1+1]
        }
      ]
    },
    {
      "key": "TP-2",
      "status_map": [
        {
          "status": "Closed",
          "sum": 4  //[(1-1(start_default))+(5-1(start_default))=0+4]
        },
        {
          "status": "Done",
          "sum": 3 //[(5(final_default)-3)+(5(final_default)-4)=2+1]
        }
      ]
    }
  ]
}

How to achieve this use case?

user_6238977 · Answer

You can start with double $map to rewrite your nested array. You'll also need $reduce since you'll be converting an array into scalar value. Since you need to "pair" two arrays, there's a perfect operator called $zip which can be used even if arrays have different lengths. Pairing final and start for the first subdocument will return:

[ [ 6,2 ], [ 3, null ] ]

which is great because you can use $ifNull to provide a default value.

Your aggregation can look like below:

db.collection.aggregate([
    {
        $project: {
            data: {
                $map: {
                    input: "$data",
                    as: "d",
                    in: {
                        key: "$$d.key",
                        status_map: {
                            $map: {
                                input: "$$d.status_map",
                                as: "sm",
                                in: {
                                    status: "$$sm.status",
                                    sum: {
                                        $reduce: {
                                            input: {
                                                $zip: {
                                                    inputs: [ "$$sm.final", "$$sm.start" ],
                                                    useLongestLength: true
                                                }
                                            },
                                            initialValue: 0,
                                            in: {
                                                $add: [
                                                    "$$value",
                                                    {
                                                        $subtract: [
                                                            { $ifNull: [ { $arrayElemAt: [ "$$this", 0 ] }, { $arrayElemAt: [ "$$sm.final_default" , 0] } ] },
                                                            { $ifNull: [ { $arrayElemAt: [ "$$this", 1 ] }, 1 ] }
                                                        ]
                                                    }
                                                ]
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
])

Mongo Playground

How to use $subtract along with conditions to use a default value from an array, if null?

1 Answers