CODEWITHSUNDEEP
node.jsmongodbmongoose
Shreyas Kumar
Shreyas Kumar

Reputation: 109

How to return a filtered result of embedded documents through a query in mongoose

I'm looking for a query that can return only certain document inside an array that is embedded inside another array of a main document. Below is the collection stored in DB:

{
    "List": {
        "_id": "5ee1c1e6739bb23e3c54d35c",
        "capitalAccountID": "CaptialAccount-43",
        "userEmail": "[email protected]",
        "flagID": 1,
        "ledgerName": "Capital Account",
        "subLedgers": [
            {
                "openingBalance": 100000,
                "closingBalance": 90001,
                "transactions": [
                    {
                        "_id": "5ee1c1e6739bb23e3c54d35e",
                        "transactionID": "123456",
                        "date": "09/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 0,
                        "debit": 1
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d35f",
                        "transactionID": "123457",
                        "date": "10/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d360",
                        "transactionID": "123458",
                        "date": "11/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    }
                ],
                "_id": "5ee1c1e6739bb23e3c54d35d",
                "subLedgerID": 1,
                "subLedgerName": "ABC",
                "reference": "dghj",
                "rateOfDuty": 10,
                "gstApplicable": true
            },
            {
                "openingBalance": 100000,
                "closingBalance": 95001,
                "transactions": [
                    {
                        "_id": "5ee1c1e6739bb23e3c54d362",
                        "transactionID": "123459",
                        "date": "13/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 0,
                        "debit": 1
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d363",
                        "transactionID": "123450",
                        "date": "14/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d364",
                        "transactionID": "123451",
                        "date": "15/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d365",
                        "transactionID": "123452",
                        "date": "16/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 0,
                        "debit": 5000
                    }
                ],
                "_id": "5ee1c1e6739bb23e3c54d361",
                "subLedgerID": 2,
                "subLedgerName": "DEF",
                "reference": "dohj",
                "rateOfDuty": 10,
                "gstApplicable": true
            }
        ],
        "idCounter": 43,
        "__v": 0
    }
}

The code Below returns above result:

await CaptialAccount.findOne(
    {
      userEmail: req.body.userEmail,
    },
    (err, list) => {
      res.status(200).json({
        List: list
      })
    }
  );

I need to filter the "transactions" array inside "subLedgers" array based on date. For example: From "09/06/2020" to "13/06/2020". Expected Output:

{
    "List": {
        "_id": "5ee1c1e6739bb23e3c54d35c",
        "capitalAccountID": "CaptialAccount-43",
        "userEmail": "[email protected]",
        "flagID": 1,
        "ledgerName": "Capital Account",
        "subLedgers": [
            {
                "openingBalance": 100000,
                "closingBalance": 90001,
                "transactions": [
                    {
                        "_id": "5ee1c1e6739bb23e3c54d35e",
                        "transactionID": "123456",
                        "date": "09/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 0,
                        "debit": 1
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d35f",
                        "transactionID": "123457",
                        "date": "10/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    },
                    {
                        "_id": "5ee1c1e6739bb23e3c54d360",
                        "transactionID": "123458",
                        "date": "11/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 5000,
                        "debit": 0
                    }
                ],
                "_id": "5ee1c1e6739bb23e3c54d35d",
                "subLedgerID": 1,
                "subLedgerName": "ABC",
                "reference": "dghj",
                "rateOfDuty": 10,
                "gstApplicable": true
            },
            {
                "openingBalance": 100000,
                "closingBalance": 95001,
                "transactions": [
                    {
                        "_id": "5ee1c1e6739bb23e3c54d362",
                        "transactionID": "123459",
                        "date": "13/06/2020",
                        "particulars": "By-sales",
                        "voucherNumber": "asdfghjk",
                        "voucherType": "SR",
                        "credit": 0,
                        "debit": 1
                    }
                ],
                "_id": "5ee1c1e6739bb23e3c54d361",
                "subLedgerID": 2,
                "subLedgerName": "DEF",
                "reference": "dohj",
                "rateOfDuty": 10,
                "gstApplicable": true
            }
        ],
        "idCounter": 43,
        "__v": 0
    }
}

Is there an query to achieve this? Thank you.

Upvotes: 1

Views: 22

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

You have to use a pipeline for this, utilizing $filter. the query equivalent of $elemMatch can be used only to return the first item in the array that matches the condition.

db.collection.aggregate([
    {
        $match: {
            userEmail: req.body.userEmail,
        }
    },
    {
        $addFields: {
            "List.subLedgers": {
                $map: {
                    input: "$List.subLedgers",
                    as: "subLedger",
                    in: {
                        $mergeObjects: ["$$subLedger", {
                            transactions: {
                                $filter: {
                                    input: "$$subLedger.transactions",
                                    as: "transaction",
                                    cond: {
                                        $and: [
                                            {
                                                $gte: ["$$transaction.date", "start date"]
                                            },

                                            {
                                                $lte: ["$$transaction.date", "end date"]
                                            }
                                        ]
                                    }
                                }
                            }
                        }]
                    }
                }
            }
        }
    }
])

From what it looks like your transaction dates are saved as string, while this is not recommended it's fine as long as you can guarantee that they are all in the same timezone and format otherwise you're going to get unexpected results.

Upvotes: 1

Related Questions