CODEWITHSUNDEEP
javascriptmongodbmongodb-queryaggregation-frameworkaggregate
Miguel
Miguel

Reputation: 141

How to add a new field in mongodb aggregate from calculation of any of array items

I'm struggling to find out how to add a new status field in an aggregate based on a calculation of an array of items.

Currently, I do this in Angular front-end by asking both collections and iterating each element with the _.some() method of Lodash. But I want to move the calculation to the backend and I'm stuck with the MongoDB aggregate.

Input: Each Subscription (one per user) has many Contracts (one per month), and I want to calculate the status of the subscription out of its contracts.

[
  {
    "_id": "5b4d9a2fde57780a2e175agh",
    "user": "5a709da7c2ffc105hu47b254",
    "contracts": [
      {
        "_id": "5b4d9a2fde57780a2e175agh",
        "date": "2018-07-15T13:00:00.000Z",
        "totalPrice": 200,
        "totalPaid": 67,
        "isCanceled": false,
        "paymentFailed": false
      },
      {
        "_id": "5b4d9a2fde57780a2e175agh",
        "date": "2018-08-15T13:00:00.000Z",
        "totalPrice": 200,
        "totalPaid": 0,
        "isCanceled": false,
        "paymentFailed": false
      },
      {
        "_id": "5b4d9a2fde57780a2e175agh",
        "date": "2018-09-15T13:00:00.000Z",
        "totalPrice": 200,
        "totalPaid": 0,
        "isCanceled": false,
        "paymentFailed": false
      }
    ]
  }
]

Output: In this case, take the past contracts and check if the user has paid what totalPrice says (and if there weren't any payment errors). If not, the payment of the subscription is “pending”:

{
  "_id": "5b4d9a2fde57780a2e175agh",
  "user": "5a709da7c2ffc105hu47b254",
  "status": "PAYMENT_PENDING" // or "PAYMENT_ERROR" or "SUCCESS"…
}

But I cannot calculate by each array item: it gives an error if I try to use "$contracts.$.totalPaid" (“'FieldPath field names may not start with '$'.'”)

This is my step of the aggregate (testing only two status conditions):

$addFields: {
  "status": {
    $cond: [
      { $and: [
        { $lt: [ "$contracts.totalPaid", "$contracts.totalPrice" ]},
        { $eq: [ "$contracts.paymentFailed", true ] },
        { $lte: [ "$contracts.date", ISODate("2018-08-24T18:32:50.958+0000") ]},
        { $eq: [ "$contracts.2.isCanceled", false ] }
      ]},
      'PAYMENT_ERROR',
      { $cond: [
        { $and: [
          { $lt: [ "$contracts.paidAmount", "$contracts.checkout.totalPrice" ]},
          //{ $eq: [ "$contracts.paymentFailed", false ] },
          //{ $lte: [ "$contracts.subscriptionDate", ISODate("2018-08-24T18:32:50.958+0000") ]},
          { $eq: [ "$contracts.isCanceled", true ] }
        ]},
        'PAYMENT_PENDING',
        'SOMETHING_ELSE'
      ]}
    ]
  }
}

I have succeeded in calculating the status out of the Subscription's fields, but not out of its array of contracts.

I would appreciate if anybody could point me in the right direction with the aggregate framework, as other examples/questions I've found $sum/calculate but do not add new fields.

Thank you very much.

Upvotes: 2

Views: 665

Answers (1)

Miguel
Miguel

Reputation: 141

I found a way: instead of calculating directly in the $addFields step, I do several more steps.

Please, feel free to suggest improvements to the aggregate, as this is my first big agrgegate :)

Step 1: $match

Conditions of Subscriptions I'm interested in. (Use your own)

Step 2: $lookup

Join each Subscription with all its contracts:

        $lookup: {
          // Join with subscriptioncontracts collection
          "from" : "subscriptioncontracts",
          "localField" : "_id",
          "foreignField" : "subscription",
          "as" : "contracts"
        }

Step 3: $unwind

Make one document per subscription contract:

        $unwind: {
          // Make one document per subscription contract
          path : "$contracts",
          preserveNullAndEmptyArrays : false // optional
        }

Step 4: $sort

(I need something special using the last/most modern contract, so I need to sort them)

        $sort: {
          // Assure the last contract if the most modern
          "_id": 1,
          "contracts.subscriptionDate": 1
        }

Step 5: $group

Here is the magic: Add new fields with the calculation using all the subscription contracts (now each “contract” is in its own document, instead of in an array)

I need to add “subscription” because I'll need to project it as the response.

        $group: {
          // Calculate status from contracts (group by the same subscription _id)
          "_id": "$_id",
          "subscription": { "$first": "$$CURRENT" },
          "_lastContract": { $last: "$contracts" },

          "_statusPaymentPending": {
            $sum: { $cond: [
              { $and: [
                { $lt: [ "$contracts.paidAmount", "$contracts.checkout.totalPrice" ] },
                { $lt: [ "$contracts.subscriptionDate", new Date() ] },
                { $eq: [ "$contracts.paymentFailed", false ] },
                    { $eq: [ "$contracts.isCanceled", false ] }
              ]}, 1, 0
            ] }
          },

          "_statusPaymentFailed": {
            $sum: { $cond: [
              { $and: [
                { $lt: [ "$contracts.paidAmount", "$contracts.checkout.totalPrice" ] },
                { $lt: [ "$contracts.subscriptionDate", new Date() ] },
                { $eq: [ "$contracts.paymentFailed", true ] },
                    { $eq: [ "$contracts.isCanceled", false ] }
              ]}, 1, 0
            ] }
          }
        }

Step 6: $project

Here I calculate other statuses from the subscription data (not the contracts)

        $project: {
          // Calculate other statuses
          "_id": "$_id",
          "subscription": "$subscription",
          "_statusCanceled": { $cond: [ "$subscription.isCanceled", true, false ] },
          "_statusFutureStart": { $cond: [ { $gte: [ "$subscription.subscriptionStartDate", new Date() ] }, true, false ] },
          "_statusUnsubscribed": { $cond: [ { $gte: [ "$subscription.subscriptionEndDate", new Date() ] }, true, false ] },
          "_statusFinished": {
            $cond: [
              { $and: [
                { $ne: [ "$subscription.subscriptionEndDate", undefined ] },
                { $lte: [ "$subscription.subscriptionEndDate", new Date() ] }
              ]},
              true,
              false 
            ]
          },
          "_statusPaymentPending": "$_statusPaymentPending",
          "_statusPaymentFailed": "$_statusPaymentFailed",
          "_statusExtensionPending": { $cond: [ { $lte: [ "$_lastContract.expirationDate", new Date() ] }, true, false ] }
        }

Step 7: $project

And finally, I merge all statuses on one “status” field:

        $project: {
          "subscription": 1,
            // Condense all statuses into one Status field
            "status": {
              $cond: [
                "$_statusCanceled",
                'CANCELED',
                { $cond: [
                    "$_statusPaymentFailed",
                    'PAYMENT_ERROR',
                    { $cond: [
                        "$_statusPaymentPending",
                        'PAYMENT_PENDING',
                        { $cond: [
                            "$_statusUnsubscribed",
                            'UNSUBSCRIBED',
                            { $cond: [
                                "$_statusExtensionPending",
                                'PENDING_EXTEND_CONTRACT',
                                { $cond: [
                                    "$_statusFutureStart",
                                    'FUTURE_START',
                                    { $cond: [
                                        "$_statusFinished",
                                        'FINISHED',
                                        'OK'
                                    ]}
                                ]}
                            ]}
                        ]}
                    ]}
                ]}
              ]
            }
        }

TODO

Maybe you can improve my flow:

  • Instead of having a subscription and status final object, is it possible to move all the data from the subscription object to the root (accompanied by the computed status field)?
  • Do you see other better way of calculating this final status field, instead of having a $group and two $project?

Thank you for any improvement you may suggest!

Upvotes: 1

Related Questions