From two collections how to filter un matching data

Question

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?

s7vr · Accepted Answer · 2018-08-20T11:52:46.340

1

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: []
    }
  }
])

edited Aug 20 '18 at 11:52

answered Aug 20 '18 at 10:57

s7vr

73,656
11
106
127

Hi Actually this query will give all the matching documents from the items and orders. But i want output has from the orders collection and items collection the un matching item codes from the items collection should be the output. – Anjansai Aug 20 '18 at 11:15
You can switch the order. Updated answer. – s7vr Aug 20 '18 at 11:38
I think you did not get my question. From the updated query also ill get all the matching data from the orders and items collections. But my question is i need the items from the item collection which where not used even once in the orders .Then i can show the items which where not used for orders. – Anjansai Aug 20 '18 at 11:52
That is what I had initially. Reverted. Did you try my answer ? Note the `$match` stage it only lists the items where there is no match orders `[]` – s7vr Aug 20 '18 at 11:54
But this is possible in mongodb version less than 4.0 ?. If not how to get the output from 3.2 – Anjansai Aug 20 '18 at 12:13
Its possible in 3.2 if you can fix your collection documents to contain same data type for itemcode. – s7vr Aug 20 '18 at 12:17
Can you share me the query for 3.2 version – Anjansai Aug 20 '18 at 12:29
You can follow the [post](https://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) to convert the string to numerical in orders collection. Once you are done you can use the query without the `addFields` stage and replace the `lookup'`s `localField` with `itemCode` – s7vr Aug 20 '18 at 12:30

From two collections how to filter un matching data

1 Answers1