CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
Anjansai
Anjansai

Reputation: 47

From two collections how to filter un matching data

In DB i have som sample data as fallows items(Collection name)

//Object 1
{
    "_id" : 1234,
    "itemCode" : 3001,// (Number)
    "category" : "Biscuts"
}
//Object 2
{
    "_id" : 1235,
    "itemCode" : 3002,// (Number)
    "category" : "Health products"
}

The Above is the sample data in the items collection. So like this, there are many objects with the unique item code. orders(Collection name)

{
    "_id" : 1456,
    "customer" : "ram",
    "address" : "india",
    "type" : "order",
    "date" : "2018/08/20",
    "orderId" : "999",
    "itemcode" : "3001"//('string')
}

The above is the orders sample data. Even this collection has many objects with repeating item codes and orderid. In the application, we have some tab called items not billed. So in this tab, we can see the items which were not used even once for the order. So from the above data how can I show the items which were not used? For example: From the above data the resulting itemcode should be 3002 because that item is not used even once. How can I get the output with one DB query?

Upvotes: 0

Views: 432

Answers (1)

s7vr
s7vr

Reputation: 75984

You can use below aggregation in mongo 4.0 version. 

db.items.aggregate([
  { $addFields: {
     itemCodeStr: {$toString: "$itemCode"}
  }},
  {
    $lookup: {
     from: "orders", 
     localField: "itemCodeStr", 
     foreignField: "itemcode",
     as: "matched-orders"
    }
  },
  {
    $match: {
      matched-orders: []
    }
  }
])

Upvotes: 1

Related Questions