CODEWITHSUNDEEP
node.jsmongodbmongoose
Sarus Crane
Sarus Crane

Reputation: 336

mongo db how to join collections

How to fetch records as join full outer in mongo db 3.2

inventory collection

db.inventory.insert([
{ "_id" : 1, "coin_id" : "12345", description: "product 1"},
{ "_id" : 2, "coin_id" : "12346", description: "product 2"},
])

Order Collection

db.orders.insert([
{ "_id" : 1, "coin_id" : "12345", "user_id" : 11, "bal" :1000 },
])

Here Scenario is like that. i want to fetch reocrd from inventory collection on the basis of coin_id,then matching record from order collection. but if not matched on order collection , it will be give at least inventory records .

My code -:

db.inventory.aggregate([
{
    $lookup:
    {
       from: 'orders',
       localField: 'coin_id',
       foreignField: 'coin_id',
       as: 'coin_details'
    }
},  {$match : {'coin_details.user_id':'11')}}

I always want to records from inventory records whenever either user_id will empty or not available If any other possibility available please let me know. please guide me how to do it.

Upvotes: 1

Views: 192

Answers (2)

Alex Blex
Alex Blex

Reputation: 37018

The $match condition should be changed to meet requirements "matching user id, or no matching order" :

db.inventory.aggregate([
    { 
        $lookup:
        {
            from: 'orders',
            localField: 'coin_id',
            foreignField: 'coin_id',
            as: 'coin_details'
        }
    },  
    { $match : {$or: [
        {'coin_details.user_id':11}, 
        {'coin_details':{$size:0}}
    ] } }
] )

As a side note, user_id is a number in the sample document, so it should be in the query.

Upvotes: 2

Saravana
Saravana

Reputation: 12817

remove the $match pipeline, it will return the inventory document with empty array of order if not match found

db.inventory.aggregate([
{
    $lookup:
    {
       from: 'orders',
       localField: 'coin_id',
       foreignField: 'coin_id',
       as: 'coin_details'
    }
}
])

Upvotes: 0

Related Questions