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:
- Do a
$lookupbetween merchants and transactions asmerchantTransactions addField"hasHolds" with acond- The
condchecks thesizeofmerchantTransactions.statusarray for each merchant. sizeruns afilterwhich only selects an element of themerchantTransactions.statusarray 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?