In MongoDB how to use $lookup to get not matched records only?

Question

In Mongodb, I want to get data of those products who don't have any order.

Collections : master_product_details, master_order_details

I'm using normal $lookup query which is giving all records of matched or unmatched with order.

db.master_product_details.aggregate([
        { 
        $match: { seller_user_id : 'seller_id' } 
        },
        {
        $lookup : {from: "master_order_details",localField: "seller_sku_id", foreignField: "sku_id", as : "Orders"} 
        },
        {$unwind : '$Orders'},
        {$project : { seller_sku_id : 1, product_title : 1, _id : 0}

            }
        ])

Any other way to get result ?

use one more `$match` condition at the end. `{ $match: { Orders: [] }}` — Ashh, Jan 25 '19 at 09:33
Yes, Thank you @AnthonyWinzlet, I just replace {$unwind : '$Orders'}, with { $match: { Orders: [] }} and got my desired result. — Sourabh Bhutani, Jan 25 '19 at 11:16

score 16 · Accepted Answer · answered Jan 25 '19 at 18:54

16

Use one more $match condition at the end of the pipeline

db.master_product_details.aggregate([
  { "$match": { "seller_user_id": "seller_id" }},
  { "$lookup": {
    "from": "master_order_details",
    "localField": "seller_sku_id",
    "foreignField": "sku_id",
    "as": "Orders"
  }},
  { "$match": { "Orders": [] }}
])

answered Jan 25 '19 at 18:54

Ashh

44,693
14
105
132

1

In case you needed only matching records(as was case with our requirements), the second match condition would have been: { "$match": { "Orders": { $exists: true, $ne: [] } } } courtesy: https://stackoverflow.com/questions/14789684/find-mongodb-records-where-array-field-is-not-empty – JavaTec Feb 08 '21 at 16:48

In MongoDB how to use $lookup to get not matched records only?

1 Answers1