CODEWITHSUNDEEP
mongodbmongodb-query
Mario Nommac
Mario Nommac

Reputation: 13

MongoDB: projection $ when find document into nested arrays

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?

Upvotes: 1

Views: 580

Answers (1)

mickl
mickl

Reputation: 49945

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.

Upvotes: 1

Related Questions