Mongodb aggregation - lookup and filter by root _id in joined array

user_18577232 0
Viewed 125

I have aggregation as you can see below. The aggregation works over Invoices table. I just join notifications and then try to filter invoices by notifications.

I would like to get invoices for which notifications have not yet been sent. Other words - give me invoices which have not joined notification with state "SENT", serviceType "EMAIL", type "BILLING_INVOICE" and assigned the invoice. The main problem is the attribute invoice and referencing to _id. I also tried reference by $_id or $$data._id, but nothing works. What is right solution? Thank you.

Example of proper functioning

Invoices

[{_id: 123, order: 333}]

Notifications

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

Returned invoices - [{_id: 123}]

Invoices

[{_id: 123, order: 333}]

Notifications

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

Returned invoices - []

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"
                    }
                }
            }
        }
    }
]);
mongoose mongodb-query mongodb aggregate
1 Answers

Your conditions are not fully clear to me do you like to combine the conditions by OR or AND?

Solution could be like this one:

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

You can filter notifications already inside the $lookup, might give better performance:

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"
      }
   }
])
user_30272660
Related