Match and add new field to each array elements in Mongodb aggregation

Question

I have two collections below and I'm creating a view table by using the aggregation and merge.

account table:
{
    "_id": {
        "$oid": "5f61b573db16185cebfeaaf9"
    },
    "status": "ACTIVE",
    "personalInfo": {
        "entityName": "John Doe",
        ...
    },
    "createdDateTime": {
        "$date": "2020-09-16T06:49:23.103Z"
    },
    "modifiedDateTime": {
        "$date": "2020-09-16T06:49:23.103Z"
    }
}

account_transactions:
{
    "_id": {
        "$oid": "5f61b5b3db16185cebfeab0c"
    },
    "accountId": "5f61b573db16185cebfeaaf9",
    "transactionType": "Payment",
    "transactionStatus": "NEW",
    "createdDate": {
        "$date": "2020-09-16T06:50:27.305Z"
    },
    "createdBy": "[email protected]",
    "lastModifiedDate": {
        "$date": "2020-09-16T10:23:06.617Z"
    }
}

{
    "_id": {
        "$oid": "5f61b5b3db16185cebfeab0f"
    },
    "accountId": "5f61b573db16185cebfeaaf9",
    "transactionType": "Credit",
    "createdDate": {
        "$date": "2020-09-16T06:50:27.305Z"
    },
    "createdBy": "[email protected]",
    "lastModifiedDate": {
        "$date": "2020-09-16T10:23:06.617Z"
    }
}

Below mongodb query creates a view table with each account having multiple transactions if exist and if no transactions exists then adds dummy transaction to with some dummy fields.

db.account.aggregate(
    [
        {
            $match: {
                'status': 'ACTIVE'
            }
        },

        {$addFields: {"accountId": {"$toString": "$_id"}}},

        {
            $lookup: {
                from: "account_transactions",
                localField: "accountId",
                foreignField: "accountId",
                as: "transactions"
            }
        },
        {
            "$project": {
                "accountType": 1,
                "transactions": {
                    "$cond": [
                        { "$eq": [ "$transactions", [] ] },
                        {$concatArrays: [[{"lastModifiedDate": {$arrayElemAt: ["$account.modifiedDateTime",0]}, "transactionType" : "BLANK"}]] },//adds dummy fields
                        {"$filter": {
                        "input": "$transactions",
                        "as": "transactions",
                        "cond": {
                            "$or": [
                                {
                                    "$eq": [
                                        "$$transactions.transactionType",
                                        "Payment"
                                    ]
                                },
                                {
                                    "$eq": [
                                        "$$transactions.transactionType",
                                        "Sale"
                                    ]
                                },
                                {
                                    "$eq": [
                                        "$$transactions.transactionType",
                                        "Debit"
                                    ]
                                }

                            ]
                        }
                    }
                    }
                },
                "createdBy": 1
            }
        },
        {$merge: {into: "account_view_table", on: "_id", whenMatched: "replace", whenNotMatched: "insert"}}
    ])

What I need to do now is to add a new field in the view table is created above for each transactions by matching transaction type. For example: if an account has multiple transactions each with different transaction type, then check the transactionType of each transactions and add new fields "transactionTypeOrder" on each like:

I've been trying different things but nothing is working

 {$addFields: {"transactions.transactionTypeOrder" : 
             {
                 $cond: [{$in:["$transactions.transactionType",["Payment"]]},"1",
                     {$cond: [ {$in:["$transactions.transactionType",["Sale"]]},"2",
                             {$cond: [{$in:["$transactions.transactionType",["Debit"]]},"3", "4" ]}
                             ]}
                             ]
             }
 }},

So if transactionType == "Payment" add transactionTypeOrder: 1, if transactionType == "Sales" add transactionTypeOrder: 2 etc. The output I'm looking for is something like below:

Case for 2nd Case checking another field also : In the same transactions, I also have to do another check with different field to check if "transactionStatus" field exists and if not exists add "transactionStatusOrderAsc" field as "####" and "transactionStatusOrderDsc" as "~~~" and if exist same value as "transactionStatus" So the output will be as below:

So basically, I have to check transactionType and add new Filed and also at the same time check transactionStatus and two need fields.

 //result view table
        {
            "_id": {
                "$oid": "5f61b573db16185cebfeaafd"
            },
            //...other fields.
            "transactions": [{
                "transactionType": "Payment",
                "transactionTypeOrder": 1,
                "transactionStatus": "New",
                "transactionStatusOrderAsc": "New",
                "transactionStatusOrderDsc": "New"
            },
        
            {
                "transactionType": "Sales",
                "transactionTypeOrder": 2,
                "transactionStatusOrderAsc": "####", //since transactionStatus field doesn't exist here
                "transactionStatusOrderDsc": "~~~~"
            },
        
            {
                "transactionType": "Debit",
                "transactionTypeOrder": 3,
                "transactionStatus": "Old",
                "transactionStatusOrderAsc": "Old",
                "transactionStatusOrderDsc": "Old"
            }
        
            ]
        }

matthPen · Accepted Answer · 2020-09-18 05:44:01Z

2

You can use $map to apply to each element of your array, and $switch to check value and add your field.

db.account.aggregate([
  {
    $match: {
      "status": "ACTIVE"
    }
  },
  {
    $addFields: {
      "accountId": {
        "$toString": "$_id"
      }
    }
  },
  {
    $lookup: {
      from: "account_transactions",
      localField: "accountId",
      foreignField: "accountId",
      as: "transactions"
    }
  },
  {
    "$project": {
      "accountType": 1,
      "createdBy": 1,
      transactions: /**adds dummy fields*/
      {
        "$map": {
          "input": "$transactions",
          "as": "transactions",
          "in": {
            $switch: {
              branches: [
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Payment"
                    ]
                  },
                  then: {
                    transactionType: "$$transactions.transactionType",
                    transactionTypeOrder: 1
                  }
                },
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Sales"
                    ]
                  },
                  then: {
                    transactionType: "$$transactions.transactionType",
                    transactionTypeOrder: 2
                  }
                },
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Debit"
                    ]
                  },
                  then: {
                    transactionType: "$$transactions.transactionType",
                    transactionTypeOrder: 3
                  }
                },
                
              ],
              default: {
                transactionType: "$$transactions.transactionType",
                transactionTypeOrder: -1
              }
            }
          }
        }
      }
    }
  },
])

If you need other field of each transaction, you have to add them in each case condition. You can test it here

EDIT FROM FIRST COMMENT

If you have to include all your transactions fields, you can use $mergeObjects instead of manually creating your object.

db.account.aggregate([
  {
    $match: {
      "status": "ACTIVE"
    }
  },
  {
    $addFields: {
      "accountId": {
        "$toString": "$_id"
      }
    }
  },
  {
    $lookup: {
      from: "account_transactions",
      localField: "accountId",
      foreignField: "accountId",
      as: "transactions"
    }
  },
  {
    "$project": {
      "accountType": 1,
      "createdBy": 1,
      transactions: /**adds dummy fields*/
      {
        "$map": {
          "input": "$transactions",
          "as": "transactions",
          "in": {
            $switch: {
              branches: [
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Payment"
                    ]
                  },
                  then: {
                    $mergeObjects: [
                      "$$transactions",
                      {
                        transactionTypeOrder: 1
                      }
                    ]
                  }
                },
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Sales"
                    ]
                  },
                  then: {
                    $mergeObjects: [
                      "$$transactions",
                      {
                        transactionTypeOrder: 2
                      }
                    ]
                  }
                },
                {
                  case: {
                    $eq: [
                      "$$transactions.transactionType",
                      "Debit"
                    ]
                  },
                  then: {
                    $mergeObjects: [
                      "$$transactions",
                      {
                        transactionTypeOrder: 3
                      }
                    ]
                  }
                },
                
              ],
              default: {
                $mergeObjects: [
                  "$$transactions",
                  {
                    transactionTypeOrder: -1
                  }
                ]
              }
            }
          }
        }
      }
    }
  },
])

Test it here

edited Sep 18, 2020 at 5:44

answered Sep 17, 2020 at 19:27

matthPen

4,3731 gold badge18 silver badges18 bronze badges

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

5 Comments

Sujal Over a year ago

Is there any way that every fields don't have to be added on every cases? There are lots of fields in the transactions and having to add them in every case and default case doesn't really seems good.

Sujal Over a year ago

Also, if I have to check one for field with similar cases with different condition, I have to put another switch case in the same $map with new condition and all the fields from transactions?

matthPen Over a year ago

I edit my answer for your first comment. Can you give an example for the second one?

Sujal Over a year ago

I've updated the question for the 2nd case above. So basically, in the same transactions first condition is to check transactionType field and add new field and second condition is to check transactionStatus field and add other two fields.

matthPen Over a year ago

2 solution for another field to set : doing another switch case in each existing case (query more complicated), or do it in another stage with the same way (impact on performances).

Collectives™ on Stack Overflow

Match and add new field to each array elements in Mongodb aggregation

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related