MongoDB: projection $ when find document into nested arrays

Question

I have the following document of collection "user" than contains two nested arrays:

{ 

    "person" : {
        "personId" : 78,
        "firstName" : "Mario", 
        "surname1" : "LOPEZ", 
        "surname2" : "SEGOVIA" 
    }, 
    "accounts" : [
        {
            "accountId" : 42,
            "accountRegisterDate" : "2018-01-04", 
            "banks" : [
                {
                    "bankId" : 1, 
                    "name" : "Bank LTD",                     
                },
                {
                    "bankId" : 2, 
                    "name" : "Bank 2 Corp",                     
                }
            ]
        }, 
        {
            "accountId" : 43, 
            "accountRegisterDate" : "2018-01-04", 
            "banks" : [
                {
                    "bankId" : 3, 
                    "name" : "Another Bank",                     
                },
                {
                    "bankId" : 4, 
                    "name" : "BCT bank",                     
                }
            ]
        }
    ]
}

I'm trying to get a query that will find this document and get only this subdocument at output:

{

    "bankId" : 3,
    "name" : "Another Bank",                     

}

I'm getting really stucked. If I run this query:

{ "accounts.banks.bankId": "3" }

Gets the whole document. And I've trying combinations of projection with no success:

{"accounts.banks.0.$": 1} //returns two elements of array "banks"

{"accounts.banks.0": 1}   //empty bank array

Maybe that's not the way to query for this and I'm going in bad direction.

Can you please help me?

mickl · Accepted Answer · 2018-01-11 22:03:21Z

1

You can try following solution:

db.user.aggregate([
   { $unwind: "$accounts"  },
   { $match: { "accounts.banks.bankId": 3 } },
   {
      $project: {
         items: {
            $filter: {
               input: "$accounts.banks",
               as: "bank",
               cond: { $eq: [ "$$bank.bankId", 3 ] }
            }
         }
      }
   },
   { 
     $replaceRoot : {
        newRoot: { $arrayElemAt: [ "$items", 0 ] }
     } 
   }
])

To be able to filter accounts by bankId you need to $unwind them. Then you can match accounts to the one having bankId equal to 3. Since banks is another nested array, you can filter it using $filter operator. This will give you one element nested in items array. To get rid of the nesting you can use $replaceRoot with $arrayElemAt.

answered Jan 11, 2018 at 22:03

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

1 Comment

Mario Nommac Over a year ago

That works perfect!. I didn't know about the $unwind aggregation. Thanks a lot

Collectives™ on Stack Overflow

MongoDB: projection $ when find document into nested arrays

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related