How can i do Mongodb aggrigate filter for multiple collection?

Question

I have two collection bookings and invoices and I have prepared aggregate and lookup query for below condition

bookings collection condition

condition 1: Status not equal to delivered

condition 2: products should not be null or empty

condition 3: ProductID should exist in the products array and should not be null

condition 4: IsDeliveryFailed should not be "Yes"

bookings Collection Data

{
    "_id" : ObjectId("609a382b589346973c84c6fe"),
    "Name" : "abc",
    "UserId":1
    "Status" : "Pending", 
    "Invoices" : [ 
        ObjectId("709a382b5c6fe89346973c84")
    ],  
    "BookingData" : {
        "Date" : ISODate("2021-04-30T04:00:00.000Z"),
        "info" : [],
        "BookingDataMethod" : "avf",
        "Message" : null,
        "products" : [ 
            {
                "_id" : ObjectId("60a4e92775e5de3570578820"),
                "ProductName" : "Test1",
                "ProductID" : ObjectId("60a4e92475e5de357057880a"),
                "IsDeliveryFailed" : "Yes"
            }, 
            {
                "_id" : ObjectId("60a4e92775e5de357057881f"),
                "ProductName" : "Test2",
                "ProductID" : ObjectId("60a4e92475e5de357057880d")
            }
        ],
        
    }
    
}

invoices collection condition

condition 1: InvoiceData should not be null or empty

condition 2: InvoiceID should exist in the InvoiceData array and should not be null

condition 3: IsPaymentFailed should not be "Yes"

invoices Collection Data

{
    "_id" : ObjectId("709a382b5c6fe89346973c84"),    
    "invoiceNumber":1
    "InvoiceData" :[
            {
                "_id" : ObjectId("60a4e92775e5de3570578820"),
                "ProductName" : "Test1",
                "InvoiceID":1,
                "IsPaymentFailed" : "Yes"
            }, 
            {
                "_id" : ObjectId("60a4e92775e5de357057881f"),
                "InvoiceID":2,
                "ProductName" : "Test2",
                
            }
    
    ]
    
}

Query

db.bookings.aggregate([
  {
    "$match": {
      "Status": {
        $ne: "Delivered"
      }
    }
  },
  {
    "$lookup": {
      "from": "invoices",
      "localField": "Invoices",
      "foreignField": "_id",
      "as": "invoiceInfo"
    }
  },
  {
    "$match": {
      "$or": [
        {
          "BookingData.products": {
            "$exists": true
          }
        },
        {
          "invoiceInfo.InvoiceData": {
            "$exists": true
          }
        }
      ]
    }
  },
 {
    $set: {
      "BookingData.products": {
        "$filter": {
          "input": "$BookingData.products",
          "cond": {
            $and: [
              { $ne: [ "$$this.ProductID", undefined ] },
              { $ne: [ "$$this._id", null ] },
              { $ne: [ "$$this.IsDeliveryFailed", "Yes" ] }
            ]
          }
        }
      }
    }
  },
 {
    $set: {
      "invoiceInfo.InvoiceData": {
        "$filter": {
          "input": "$invoiceInfo.InvoiceData",
          "cond": {
            $and: [
              { $ne: [ "$$this.InvoiceID", undefined ] },
              { $ne: [ "$$this._id", null ] },
              { $ne: [ "$$this.IsPaymentFailed", "Yes" ] }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $or: [
          {
            $ne: [
              "$BookingData.products",
              [],
              
            ]
          },
          {
            $ne: [
              "$invoiceInfo.InvoiceData",
              [],
              
            ]
          }
        ]
      }
    }
  }
  
])

this is not working as expected for example if

The query should return the above document

if ProductID exist and products exist and all products do not have IsDeliveryFailed: "Yes"

if ProductID exist and products exist and anyone products do not have IsDeliveryFailed: "Yes"

if InvoiceID exist and InvoiceData exist and all InvoiceData do not have IsPaymentFailed: "Yes"

if InvoiceID exist and InvoiceData exist and anyone InvoiceData does not has IsPaymentFailed: "Yes"

another set

if ProductID exists and products exist and all products are IsDeliveryFailed: "Yes" flag. but we have to check invoices collection if InvoiceID exist and InvoiceData exist and anyone InvoiceData does not have IsPaymentFailed: "Yes" then we have to return this document

if InvoiceID exist and InvoiceData exist and all InvoiceData are IsPaymentFailed: "Yes".but we have to check bookings collection if ProductID exist and products exist and anyone products does not have IsDeliveryFailed:"Yes" then we have to returndocument

Mongo playground

user_7975771 · Answer

I have explained your previous problem in Question. Since the invoiceInfo is an array and invoiceData also an array inside invoiceInfo, we use map and filter. Then we need to exclude empty array of invoiceData. (This can be done in previous step also like filter-map->filter, but it could be lengthy, thats why I used it in next stage )

Here is the code

db.bookings.aggregate([
  {
    "$match": {
      "PaymentStatus": { $ne: "Delivered" }
    }
  },
  {
    $set: {
      "BookingData.products": {
        "$filter": {
          "input": "$BookingData.products",
          "cond": {
            $and: [
              { $ne: [ "$$this.ProductID", undefined ] },
              { $ne: [ "$$this._id", null ] },
              { $ne: [ "$$this.IsDeliveryFailed", "Yes" ] }
            ]
          }
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "invoices",
      "localField": "Invoices",
      "foreignField": "_id",
      "as": "invoiceInfo"
    }
  },
  {
    $set: {
      invoiceInfo: {
        $map: {
          input: "$invoiceInfo",
          as: "info",
          in: {
            InvoiceData: {
              $filter: {
                input: "$$info.InvoiceData",
                as: "data",
                "cond": {
                  $and: [
                    { $ne: [ "$$data.InvoiceID", undefined ] },
                    { $ne: [ "$$data.InvoiceID", null ] },
                    { $ne: [ "$$data.IsPaymentFailed", "Yes" ] }
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $set: {
      invoiceInfo: {
        $filter: {
          input: "$invoiceInfo",
          cond: { $ne: [ "$$this.InvoiceData", [] ] }
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $or: [
          { $ne: [ "$BookingData.products", [] ] },
          { $ne: [ "$invoiceInfo", [] ] }
        ]
      }
    }
  }
])

Working Mongo playground

Hope this will help you. This is the time you need to play/ workaround based on your requirement. Sometimes you need to do lookup before or after the current location in demo

How can i do Mongodb aggrigate filter for multiple collection?

1 Answers