构建满足指定条件的MongoDB Customer集合聚合管道

一、Customer文档结构 #

{
  "_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"}
}

二、SQL风格筛选条件 #

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

三、MongoDB聚合管道 #

注：先修正SQL逻辑顺序（WHERE应在GROUP BY之前），再转换为MongoDB聚合管道，同时处理日期相关逻辑（如"today"取当日日期部分、"tomorrow"取次日日期部分）：

[
  // 过滤创建时间在90天内的文档（对应SQL的WHERE条件）
  {
    $match: {
      $expr: {
        $lt: [
          { $subtract: [new Date(), "$created_date"] },
          90 * 24 * 60 * 60 * 1000 // 90天对应的毫秒数
        ]
      }
    }
  },
  // 按email分组，计算聚合值并保留组内文档信息
  {
    $group: {
      _id: "$email",
      maxCreatedDate: { $max: "$created_date" },
      maxStatusCode: { $max: "$status_code" },
      maxLastModifiedDate: { $max: "$last_modified_date" },
      docs: {
        $push: {
          user_id: "$user_id",
          transaction: "$transaction",
          created_date: "$created_date"
        }
      }
    }
  },
  // 按HAVING条件筛选分组结果
  {
    $match: {
      $and: [
        // max(createdDate) 等于今日（仅比较日期部分，忽略时分秒）
        {
          $expr: {
            $eq: [
              { $dateTrunc: { date: "$maxCreatedDate", unit: "day" } },
              { $dateTrunc: { date: new Date(), unit: "day" } }
            ]
          }
        },
        // max(status_code) < 5
        { maxStatusCode: { $lt: 5 } },
        // max(lastModifiedDate)加2天后 小于明日（仅比较日期部分）
        {
          $expr: {
            $lt: [
              { $dateAdd: { date: "$maxLastModifiedDate", days: 2 } },
              { $dateTrunc: { date: { $dateAdd: { date: new Date(), days: 1 } }, unit: "day" } }
            ]
          }
        }
      ]
    }
  },
  // 从组内文档中匹配对应maxCreatedDate的记录（获取user_id和transaction）
  {
    $addFields: {
      targetDoc: {
        $first: {
          $filter: {
            input: "$docs",
            cond: { $eq: ["$$this.created_date", "$maxCreatedDate"] }
          }
        }
      }
    }
  },
  // 投影出需要的字段
  {
    $project: {
      _id: 0,
      user_id: "$targetDoc.user_id",
      email: "$_id",
      transaction: "$targetDoc.transaction",
      created_date: "$maxCreatedDate"
    }
  },
  // 按指定字段排序
  {
    $sort: {
      created_date: -1, // 降序
      user_id: 1        // 升序
    }
  }
]

内容的提问来源于stack exchange，提问作者Andulos