How do i use an aggregation pipeline to identify orders which do not have descriptions-CodePudding

I have 2 collections which have 2 fields, i.e. orderID and productCode which are the keys that form the primary keys in 2 collections, orders and productInfo. The keys orderID and productCode are present in both collections. I want to be able to find orders whose descriptions are present. The description for any order is present if both its orderID and productCode are present in the productInfo collection.

For example, "apples" and "oranges" have descriptions in the productInfo collection, but "bananas" and "milk" do not. I want to be able to return the orderID and productCode for items which do not have descriptions.

I read that I can use an aggregation pipeline to join the collections but despite looking at examples, I am not able to find out how to use the 2 keys to accomplish this. I am very new to using mongodb so I'd appreciate if someone could help me. I know I can use a lookup but I discovered that I can only use one localField per lookup and that makes it difficult to figure out. I'd be really grateful if someone could help me figure this out. I also saw the way to match using $ne but my limited knowledge makes it hard to see how to apply. Here are the collections in question:

Orders collection


      db.orders.insertMany( [

     { "_id" : 1102, "orderID" : 10340, "productCode": 1, "item" : "apple" },

     { "_id" : 3332, "orderID" : 10341, "productCode": 2,  "item" : "banana"},

     { "_id" : 3443, "orderID" : 10342, "productCode": 3, "item" : "orange" },

     { "_id" : 4332, "orderID" : 10343, "productCode": 4, "item" : "milk" }
   
    ] )

And here is the productInfo collection


    db.productInfo.insertMany( [

 
    { "_id" : 1102, "orderID" : 10340, "productCode": 2,  "description" : "granny smith apples"},


    { "_id" : 3443, "orderID" : 10342, "productCode": 4, "description" : "blood oranges from Florida" }
   
  ] )

Any help will be much appreciated.

CodePudding user response：

Based on your question and comments, I think you want:

"I want to be able to return the orderID and productCode for items which do not have descriptions." ... "The description for any order is present if both its orderID and productCode are present in the productInfo collection."

Here's one way to do that.

db.orders.aggregate([
  {
    "$lookup": {
      "from": "productInfo",
      "let": {
        "orderID": "$orderID",
        "productCode": "$productCode"
      },
      "as": "productInfo",
      "pipeline": [
        { // look for matches in productInfo
          "$match": {
            "$expr": {
              "$and": [
                {"$eq": ["$orderID", "$$orderID"]},
                {"$eq": ["$productCode", "$$productCode"]}
              ]
            }
          }
        },
        { // if matched, no need to return everything
          "$project": {
            "_id": 1
          }
        }
      ]
    }
  },
  { // match when no productInfo found
    "$match": {
      "$expr": {
        "$eq": [{"$size": "$productInfo"}, 0]
      }
    }
  },
  { // output requested fields
    "$project": {
      "_id": 0,
      "orderID": 1,
      "productCode": 1
    }
  }
])

Try it on mongoplayground.net.