Is there a better way to adding a field based on the result of aggregation in MongoDB

Viewed 27

I have the following structure in my Mongo:

Merchants
 - name
 - _id

Transactions:
 - _id
 - status
 - merchantId

Every merchant can have multiple transactions. I want to be able to add a field (to the result of aggregate to get all merchants) called hasHolds and set it to true if any of the transactions belonging to a merchant are in the status "capture_pending".

Here is how I achieved it:

  1. Do a $lookup between merchants and transactions as merchantTransactions
  2. addField "hasHolds" with a cond
  3. The cond checks the size of merchantTransactions.status array for each merchant.
  4. size runs a filter which only selects an element of the merchantTransactions.status array if its "capture_pending"

Thus: collections:

db={
  "merchants": [
    {
      "name": "m1",
      "_id": "5b53494987eea4171d199dd3"
    },
    {
      "name": "m2",
      "_id": "5b53495087eea4171d199dd4"
    },
    {
      "name": "m3",
      "_id": "5b53495987eea4171d199dd5"
    }
  ],
  "transactions": [
    {
      "_id": "5b53494987eea4171d199dd4",
      "merchantId": "5b53494987eea4171d199dd3",
      "status": "capture_pending"
    },
    {
      "_id": "5b53494987eea4171d199dd0",
      "merchantId": "5b53494987eea4171d199dd3",
      "status": "done"
    },
    {
      "_id": "5b53494987eea4171d199dd8",
      "merchantId": "5b53495987eea4171d199dd5",
      "status": "capture_pending"
    },
    {
      "_id": "5b53494987eea4171d199de8",
      "merchantId": "5b53495987eea4171d199dd5",
      "status": "capture_pending"
    },
    {
      "_id": "5b53494987eea4171d199dd9",
      "merchantId": "5b53495087eea4171d199dd4",
      "status": "done"
    },
    {
      "_id": "5b53494987eea4171d199de9",
      "merchantId": "5b53495087eea4171d199dd4",
      "status": "done"
    }
  ]
}

Query:

db.merchants.aggregate([
  {
    "$lookup": {
      from: "transactions",
      localField: "_id",
      foreignField: "merchantId",
      as: "merchantTransactions",
      
    }
  },
  {
    "$addFields": {
      "hasHolds": {
        "$cond": {
          "if": {
            "$gt": [
              {
                $size: {
                  "$filter": {
                    "input": "$merchantTransactions.status",
                    "as": "st",
                    "cond": {
                      "$eq": [
                        "$$st",
                        "capture_pending"
                      ]
                    }
                  }
                }
              },
              0
            ]
          },
          "then": true,
          "else": false
        }
      }
    }
  }
])

Run it here: https://mongoplayground.net/p/gJl-ot1TueB

Is there a better or easier way to achieve this?

1 Answers

You can just use $in operator in condition,

  • field merchantTransactions.status will return array of status and with $in condition it will quickly check your input status is in array or not,
  {
    "$addFields": {
      "hasHolds": {
        $in: ["capture_pending", "$merchantTransactions.status"]
      }
    }
  }

Playground

Related