How to perform lookup in aggregation when foreignField is in array?

Question

I have two collections:

// users

{
    _id: "5cc7c8773861275845167f7a",
    name: "John",
    accounts: [
        { 
            "_id": "5cc7c8773861275845167f76", 
            "name": "Name1", 
        },
        { 
            "_id": "5cc7c8773861275845167f77", 
            "name": "Name2", 
        }
    ]
}

// transactions

{
    "_id": "5cc7c8773861275845167f75",
    "_account": "5cc7c8773861275845167f76",
}

Using lookup I want to populate _account field in transactions collection with respective element from users.accounts array.

So, I want the final result as:

{
    "_id": "5cc7c8773861275845167f75",
    "_account": { 
        "_id": "5cc7c8773861275845167f76", 
        "name": "Name1", 
    },
}

I have already tried using this code:

db.transactions.aggregate([
   {
     $lookup:
       {
         from: "users.accounts",
         localField: "_account",
         foreignField: "_id",
         as: "account"
       }
  }
])

In the result account array comes as empty.

What is the correct way to do it ?

Ashh · Accepted Answer · 2019-04-30 11:36:10Z

1

You can use below aggregation with mongodb 3.6 and above

db.transactions.aggregate([
  { "$lookup": {
    "from": "users",
    "let": { "account": "$_account" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$$account", "$accounts._id"] } } },
      { "$unwind": "$accounts" },
      { "$match": { "$expr": { "$eq": ["$$account", "$accounts._id"] } } }
    ],
    "as": "_account"
  }},
  { '$unwind': '$_account' }
])

answered Apr 30, 2019 at 11:36

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ashwanth Madhav · Accepted Answer · 2019-04-30 11:43:44Z

Try with this

I think case 1 is better.

1)-

db.getCollection('transactions').aggregate([
{
$lookup:{
from:"user",
localField:"_account",
foreignField:"accounts._id",
as:"trans"
}    
},
{
$unwind:{
    path:"$trans",
    preserveNullAndEmptyArrays:true
    }
},
{
$unwind:{
    path:"$trans.accounts",
    preserveNullAndEmptyArrays:true
    }
},
{$match: {$expr: {$eq: ["$trans.accounts._id", "$_account"]}}},
{$project:{
_id:"$_id",
_account:"$trans.accounts"
}}
])

2)-

db.getCollection('users').aggregate([
{
$unwind:{
path:"$accounts",
preserveNullAndEmptyArrays:true
}    
},
{
$lookup:
{
 from: "transactions",
 localField: "accounts._id",
 foreignField: "_account",
 as: "trans"
}
},
{$unwind:"$trans"},
{
$project:{
    _id:"$trans._id",
    _account:"$accounts"
    }
}
])

Collectives™ on Stack Overflow

How to perform lookup in aggregation when foreignField is in array?

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related