Is it possible to do $lookup with an internal array based on a condition within a mongo document-CodePudding

I have list of clientSubscription, each clientSubscriptions has an array of subscription.The activeSubNo indicates which subscription is live and upcomingSubNo indicates the upcoming/next subscription.

{    
    "clientNo" : 3,  
    "activeSubNo" : 2,  
    "upcomingSubNo" : 3,
    "subscriptions" : [ 
        {
            "subNo" : 1, "Type" : "FREE", "startDate" : ISODate("2021-09-02"),"endDate" : ISODate("2021-11-02")
        }, 
        {
            "subNo" : 2, "Type" : "PAID", "startDate" : ISODate("2021-12-02"), "endDate" : ISODate("2022-02-04")
        }
        , 
        {
            "subNo" : 3, "Type" : "PAID", "startDate" : ISODate("2022-02-05"), "endDate" : ISODate("2022-05-04")
        } ]
}

{    
    "clientNo" : 5,  
    "activeSubNo" : 2,  
    "upcomingSubNo" : 0,
    "subscriptions" : [ 
        {
            "subNo" : 1, "Type" : "FREE", "startDate" : ISODate("2021-09-02"),"endDate" : ISODate("2021-11-02")
        }, 
        {
            "subNo" : 2, "Type" : "PAID", "startDate" : ISODate("2021-12-02"), "endDate" : ISODate("2022-02-03")
        }]
}
{    
    "clientNo" : 6,  
    "activeSubNo" : 1,  
    "upcomingSubNo" : 0,
    "subscriptions" : [ 
        {
            "subNo" : 1, "Type" : "FREE", "startDate" : ISODate("2022-01-01"),"endDate" : ISODate("2022-02-28")
        }
        ]
}

I need to build a view which is sorted based on the earliest expiring clientSubscription. So the comparison of endDate needs to be done only between activeSubscription's date between each of the records. if upcomingSubNo value is 0, then view should indicate that no new subscription has been bought.

View

Client Active-SubNo Type StartDate EndDate       Upcoming-SubNo Type StartDate EndDate
5       2          PAID   2021-12-02  2022-02-03           -
3       2          PAID   2021-12-02  2022-02-04        3      PAID 2022-02-05 2022-05-04
6       1          FREE   2022-01-01  2022-02-28           -

Is it possible to do this with a single collection like this and an aggregate query? Or do I have to break this into 2 collections(taking subscriptions as a separate collection), so that I can do the lookup ?

CodePudding user response：

Maybe this one:

db.collection.aggregate([
  {
    $set: {
      startDate: { $arrayElemAt: [ "$subscriptions.startDate", { $subtract: [ "$activeSubNo", 1 ] } ] },
      endDate: { $arrayElemAt: [ "$subscriptions.endDate", { $subtract: [ "$activeSubNo", 1 ] } ] },
      Type: { $arrayElemAt: [ "$subscriptions.Type", { $subtract: [ "$activeSubNo", 1 ] } ] },
      subscriptions: {
        $cond: {
          if: { $eq: [ "$upcomingSubNo", 0 ] },
          then: "$$REMOVE",
          else: { $arrayElemAt: [ "$subscriptions", { $subtract: [ "$upcomingSubNo", 1 ] } ] }
        }
      }
    }
  }
])

Mongo Playground