Mongodb聚合-在连接数组中按root _id查找和过滤

Question

我有聚合，如下所示。聚合在发票表上进行。我只是加入通知，然后尝试按通知过滤发票。

我想要获取尚未发送通知的发票。换句话说 - 给我未加入状态“SENT”、服务类型“EMAIL”、类型“BILLING_INVOICE”并分配发票的通知的发票。主要问题是属性发票和对 _id 的引用。我也尝试通过 $_id 或 $data._id 进行引用，但没有任何效果。什么是正确的解决方案？谢谢。

正确运行的示例

发票

[{_id: 123, order: 333}]

通知

[{_id: 345, order: 333, state: SENT, serviceType: EMAIL, type: BILLING_INVOICE, invoice: 123}]

退回的发票 - [{_id: 123}]

发票

[{_id: 123, order: 333}]

通知

[{_id: 345, order: 333, state: SENT, serviceType: EMAIL, type: BILLING_INVOICE, invoice: 555}]  

退回的发票 - []

let invoices = await this.invoiceDao.getModel().aggregate([
    // join notifications from order
    {
        $lookup: {
            from: "customer.notifications",
            localField: "order",
            foreignField: "order",
            as: "notifications"
        }
    },
    {
        $match: {
            "notifications": {
                // notification of billing invoice MUST NOT be sent
                $not: {
                    $elemMatch: {
                        "state": NotificationState.SENT,
                        "serviceType": NotificationServiceType.EMAIL,
                        "type": NotificationType.BILLING_INVOICE,
                        "invoice": "$$_id"
                    }
                }
            }
        }
    }
]);

Answer 1

我不太清楚您的条件，您喜欢通过 OR 还是 AND 组合条件？

解决方案可能如下所示：

db.invoices.aggregate([
   {
      $lookup: {
         from: "notifications",
         localField: "order",
         foreignField: "order",
         as: "notifications"
      }
   },
   {
      $set: {
         notifications: {
            $filter: {
               input: "$notifications",
               cond: {
                  $not: {
                     $and: [
                        { $eq: ["$this.serviceType", "EMAIL"] },
                        { $eq: ["$this.state", "SENT"] },
                        { $eq: ["$this.type", "BILLING_INVOICE"] }
                     ]
                  }
               }
            }
         }
      }
   },
   { $match: { notifications: [] } }
])

Mongo Playground

您可以过滤 $lookup< 中已有的通知/code>，可能会提供更好的性能：

db.invoices.aggregate([
   {
      $lookup: {
         from: "notifications",
         let: { invoice_order: "$order" },
         pipeline: [
            {
               $match: {
                  serviceType: { $ne: "EMAIL" },
                  state: { $ne: "SENT" },
                  type: { $ne: "BILLING_INVOICE" }
               }
            },
            { $match: { $expr: { $eq: ["$order", "$invoice_order"] } } } // join condition               
         ],
         as: "notifications"
      }
   }
])