Mongodb 聚合操作汇总

Mongodb 聚合操作汇总

1. 查询条件

1. 关键词 $match, $expr, $eq, $in, $cond

2. 查询

   // 场景: 如果工单被处理, 则存在ticketCreatedAt/ticketProcessStatus字段, ticketProcessStatus 表示最新状态, 如果未处理, 则取默认 processStatus
   const filter = {
     $expr: {
       $eq: [
         {
           $cond: {
             if: {
               $and: [
                 { $ifNull: ["$ticketCreatedAt", false] },
                 { $gt: ["$ticketCreatedAt", "$endTime"] },
               ],
             },
             then: "$ticketProcessStatus",
             else: "$processStatus",
           },
         },
         "未处理",
       ],
     },
   };
   
   Order.aggregate([
     {
       $match: {
         $and: [filter1, filter2],
       },
     },
     ...
   ])
   

2. 聚合管道

1. $match, $group, $project, $facet 用户行为分析(下单品类)

   await Order.aggregate([
     {
       $match: {
         $and: [filter1, filter2],
       },
     },
     {
       $group: {
         _id: "$userId",
         categories: { $addToSet: "$category" },
       },
     },
     {
       $project: {
         userId: "$_id",
         categoryCount: { $size: "$categories" },
         categories: 1,
         _id: 0,
       },
     },
     {
       $facet: {
         byUser: [
           {
             $sort: { categoryCount: -1 },
           },
         ],
         total: [
           { $unwind: "$categories" },
           {
             $group: {
               _id: null,
               allCategories: { $addToSet: "$categories" },
             },
           },
           {
             $project: {
               totalCategoryCount: { $size: "$allCategories" },
               _id: 0,
             },
           },
         ],
       },
     },
   ]),
   

2. $match, $group, $project, $multiply, $lookup, $unwind 用户行为分析(权重计算)

   await Order.aggregate([
     {
       $match: {
         $and: [filter1, filter2],
       },
     },
     {
       $group: {
         _id: {
           project: "$project",
           userId: "$userId",
         },
         orderCount: { $sum: 1 },
       },
     },
     {
       $project: {
         userId: "$_id.userId",
         project: "$_id.project",
         orderCount: 1,
         projectIndex: {
           $multiply: [
             "$orderCount",
             {
               $switch: {
                 branches: [
                   { case: { $eq: ["$_id.project", 4] }, then: 100 },
                   { case: { $eq: ["$_id.project", 3] }, then: 20 },
                   { case: { $eq: ["$_id.project", 2] }, then: 5 },
                 ],
                 default: 0, // 默认权重，可调整
               },
             },
           ],
         },
         _id: 0,
       },
     },
     {
       $group: {
         _id: "$project",
         projectIndex: { $sum: "$projectIndex" },
       },
     },
     {
       $lookup: {
         from: "projects",
         localField: "_id",
         foreignField: "_id",
         as: "project",
       },
     },
     // {
     //   $unwind: "$project",
     // },
     {
       $unwind: {
         path: "$project",
         preserveNullAndEmptyArrays: true,
       },
     },
     {
       $project: {
         projectId: "$_id",
         projectName: "$project.title",
         projectIndex: 1,
       },
     },
     {
       $sort: {
         projectIndex: -1,
       },
     },
     {
       $limit: 10,
     },
   ]),
   

3. $push, $arrayToObject 聚合返回list

   {
     $group: {
       _id: "$project",
       projectIndex: { $sum: "$projectIndex" },
       infoArray: {
         $push: {
           k: { $toString: "$level" },
           v: "$count",
         },
       },
     },
   },
   {
     $project: {
       _id: 0,
       project: "$project",
       infoArray: { $arrayToObject: "$infoArray" },
       projectIndex: 1,
     },
   },
   ...