Creating a MongoDB Aggregation Pipleine for a query

Question

Below is the document schema for Customer. It has nested 'transaction'

{
    "_id": {
        "$oid": "65e797c4d4ed4faa9d4e8425"
    },
    "user_id": "Test1_R_1",
    "email": "lkasdjkhsdkhxsaun+6zcYYU",
    "full_name": "iusakhlknsa==",
    "omnture_hash_key": "Test1_R_1",
    "language": "EN",
    "transaction": {
        "RESPONSE_CODE": "000",
        "SETTLEMENT_CODE": "3",
        "GENDER_FLG": "1",
        "TERM_CITY": "SIOUX FALLS",
        "CUST_AGE": "44",
        "TERM_ID": "CASHDISP_T1"
    },
    "transaction_date": {
        "$date": "2023-04-26T09:53:17.000Z"
    },
    "status": "Exported",
    "status_code": 2,
    "created_date": {
        "$date": "2024-03-05T00:48:29.716Z"
    },
    "last_modified_date": {
        "$date": "2024-03-02T00:45:11.166Z"
    }
}

Below are the conditions to filter the documents.

Select user_id, email, transaction.* from customer
Group By Email
Having (max(createdDate) = today and max(status_code) < 5) 
And 
(max(lastModifiedDate + 2) < tomorrow) 
Order by createdDate desc, user_id
Where createdDate < 90

What would be the aggregation pipeline for the above query?

Answer 1

I have no idea what you mean by created_date < 90, but nevertheless it seems as if it should go to the very top.

[
  {
    $match: {
      // created_date: { $lt: (...?) },
    },
  },
  {
    $group: {
      _id: "$user_id",
      email: { $first: "$email" },
      full_name: { $first: "$fullName" },
      transactions: {
        $push: {
          transaction: "$transaction",
          created_date: "$created_date",
        },
      },
      max_status_code: { $max: "$status_code" },
      max_created_date: { $max: "$created_date" },
      max_last_modified_date: {
        $max: "$last_modified_date",
      },
    },
  },
  {
    $unwind: { path: "$transactions" },
  },
  {
    $match: {
      max_created_date: new Date(),
      max_status_code: { $lt: 5 },
      $expr: {
        $lt: [
          {
            $add: [
              "$max_last_modified_date",
              2 * 24 * 60 * 60 * 1000,
            ],
          }, // Add 2 days in milliseconds
          {
            $toDate: {
              $add: [
                new Date(),
                24 * 60 * 60 * 1000,
              ],
            },
          }, // Tomorrow's date in milliseconds
        ],
      },
    },
  },
  {
    $sort: {
      "transaction.created_date": -1,
      user_id: 1,
    },
  },
]