Is there a way to write a Mongo query to return a date & amount?

Question

I have several "Orders" in my Mongo DB that are for various client ("Organizations"). Each Order may contain 1+ payments to be charged to a customer (e.g. Feb 1 - $200, Mar 1 - $200, etc.).

I'm trying to figure out how to write a query that looks through all the Orders, segmented by Organization and returns to me 3 values:

Input: Organization (Mongo Field: destinationId)

Output: - Date to be charged (Mongo Field: dateCharge) - Amount to be charged (Mongo Field: price) - Deposit without fees (Mongo Fields (math) - price minus totalFee)

If doing the math in the query is too difficult, then I could simply take the totalFee field and do the math in Excel/Google Sheets.

Here is a sample document that I have:

{
    "_id" : ObjectId("588e79e2e7cd645918a27c82"),
    "userId" : "5840e880b41687d46829ee26",
    "orderId" : "1001GJ",
    "updateAt" : ISODate("2017-01-29T23:25:22.566Z"),
    "createAt" : ISODate("2017-01-29T23:25:22.566Z"),
    "paymentsPlan" : [ 
        {
            "_id" : ObjectId("588e79e2e7cd645918a27c7c"),
            "description" : "Payment 1 of 4",
            "feeProcessing" : 3.92,
            "feeCollections" : 5.77,
            "totalFee" : 9.69,
            "originalPrice" : 125,
            "basePrice" : 115.31,
            "price" : 125,
            "dateCharge" : ISODate("2017-01-29T23:25:22.092Z"),
            "destinationId" : "acct_17x0WQEVwZei3oeH",
            "version" : "v2",
            "updateAt" : ISODate("2017-01-29T23:30:04.393Z"),
            "createAt" : ISODate("2017-01-29T23:25:22.520Z"),
            "paymentMethods" : [ 
                "card", 
                "bank"
            ],
            "attempts" : [ 
                {
                    "message" : "done",
                    "status" : "succeeded",
                    "dateAttemp" : ISODate("2017-01-29T23:30:04.387Z"),
                    "_id" : ObjectId("588e7afce7cd645918a27c84")
                }
            ],
            "status" : "succeeded",
            "wasProcessed" : true,
            "discountCode" : "",
            "discount" : 0
        }, 
        {
            "_id" : ObjectId("588e79e2e7cd645918a27c7d"),
            "description" : "Payment 2 of 4",
            "feeProcessing" : 3.64,
            "feeCollections" : 5.3,
            "totalFee" : 8.94,
            "originalPrice" : 115,
            "basePrice" : 106.06,
            "price" : 115,
            "dateCharge" : ISODate("2017-01-29T23:25:22.092Z"),
            "destinationId" : "acct_17x0WQEVwZei3oeH",
            "version" : "v2",
            "updateAt" : ISODate("2017-01-29T23:35:04.316Z"),
            "createAt" : ISODate("2017-01-29T23:25:22.524Z"),
            "paymentMethods" : [ 
                "card", 
                "bank"
            ],
            "attempts" : [ 
                {
                    "message" : "done",
                    "status" : "succeeded",
                    "dateAttemp" : ISODate("2017-01-29T23:35:04.310Z"),
                    "_id" : ObjectId("588e7c28e7cd645918a27c86")
                }
            ],
            "status" : "succeeded",
            "wasProcessed" : true,
            "discountCode" : "",
            "discount" : 0
        }, 
        {
            "_id" : ObjectId("588e79e2e7cd645918a27c80"),
            "description" : "Payment 3 of 4",
            "feeProcessing" : 6.97,
            "feeCollections" : 10.62,
            "totalFee" : 17.59,
            "originalPrice" : 230,
            "basePrice" : 212.41,
            "price" : 230,
            "dateCharge" : ISODate("2017-02-10T16:00:00.000Z"),
            "destinationId" : "acct_17x0WQEVwZei3oeH",
            "version" : "v2",
            "updateAt" : ISODate("2017-02-08T15:57:22.891Z"),
            "createAt" : ISODate("2017-01-29T23:25:22.528Z"),
            "paymentMethods" : [ 
                "card", 
                "bank"
            ],
            "attempts" : [],
            "status" : "pending",
            "wasProcessed" : false,
            "discountCode" : "",
            "discount" : 0
        }, 
        {
            "_id" : ObjectId("588e79e2e7cd645918a27c81"),
            "description" : "Payment 4 of 4",
            "feeProcessing" : 6.97,
            "feeCollections" : 10.62,
            "totalFee" : 17.59,
            "originalPrice" : 230,
            "basePrice" : 212.41,
            "price" : 230,
            "dateCharge" : ISODate("2017-02-24T16:00:00.000Z"),
            "destinationId" : "acct_17x0WQEVwZei3oeH",
            "version" : "v2",
            "updateAt" : ISODate("2017-02-08T15:57:26.081Z"),
            "createAt" : ISODate("2017-01-29T23:25:22.529Z"),
            "paymentMethods" : [ 
                "card", 
                "bank"
            ],
            "attempts" : [],
            "status" : "pending",
            "wasProcessed" : false,
            "discountCode" : "",
            "discount" : 0
        }
    ],
    "status" : "active",
    "description" : "Boys 18U",
    "__v" : 2
}

I know this is a rookie question but I'm self funding my business and my developer is out for a while and a client asked me for this information. Any help would be awesome.

I can get all the orders from a particular organization with this query: db.getCollection('orders').find({'paymentsPlan.destinationId':"acct_17x0WQEVwZei3oeH"}) but I'm not sure how to return data inside that order. — flipdiggity, Feb 08 '17 at 17:27

score 0 · Accepted Answer · answered Feb 09 '17 at 17:14

I worked with our developer to figure this out. Not sure if it's the most efficient but it works.

var mapFunction1 = function () {
    for (var idx = 0; idx < this.paymentsPlan.length; idx++) {
        if (this.paymentsPlan[idx].status === 'pending') {
            var key = this.paymentsPlan[idx].dateCharge.toISOString().substring(0, 10);
            var price = this.paymentsPlan[idx].price - this.paymentsPlan[idx].totalFee;
            emit(key, price);
        }

    }

};

var reduceFunction1 = function (dateCharge, price) {
    return Array.sum(price);
};

db.orders.mapReduce(
    mapFunction1,
    reduceFunction1,
    {
        out: { inline: 1 },
        query: { "paymentsPlan.destinationId": "acct_17vBpJHXmwMXUx1q"
        }
    }
)

Is there a way to write a Mongo query to return a date & amount?

1 Answers1