MongoDB: Retrieving only specific record

Question

I'm new to Mongo, and I'm trying to retrieve specific data among several with same name from a find() query:

Let me explain-> Users is collection with following document:

"linkedaccounts": [
  {
    "accountname": "Rani Charan",
    "accountnumber": "10815748690065",
    "ifsc": "UTIB0001081",
    "virtualid": "taran@xyz",
    "_id": "571488b9720023940539959e",
    "isDefaultReceiving": false,
    "isDefaultFunding": false,
    "isAppEnabled": false,
    "aeba": false,
    "accountsource": 0
  },
  {
    "accountname": "Rani Singh",
    "accountnumber": "01316366360080",
    "virtualid": "ranisingh@xyz",
    "_id": "571488b972002394053995a0",
    "ifsc": "UTIB0000131",
    "isDefaultReceiving": false,
    "isDefaultFunding": true,
    "isAppEnabled": true,
    "aeba": false,
    "accountsource": 0
  },
  {
    "accountname": "Rani K Singh",
    "accountnumber": "07916956560873",
    "virtualid": "ranik@xyz",
    "_id": "571488b9720023940539959f",
    "ifsc": "UTIB0000791",
    "isDefaultReceiving": true,
    "isDefaultFunding": false,
    "isAppEnabled": true,
    "aeba": false,
    "accountsource": 0
  }
]

now i want to fetch account number and ifsc code where virtual id is "ranisingh@xyz" . i able to get some filtered data using this query:

db.users.find(
         {"linkedaccounts.virtualid": "ranik@xyz"}, 
         {_id: 0, 'linkedaccounts.accountnumber': 1,'linkedaccounts.ifsc': 1})

but in result i get all three accountsnumber and ifsc code . like this:-

{
    "linkedaccounts" : [
            {
                    "accountnumber" : "10815748690065",
                    "ifsc" : "UTIB0001081"
            },
            {
                    "accountnumber" : "01316366360080",
                    "ifsc" : "UTIB0000131"
            },
            {
                    "accountnumber" : "07916956560873",
                    "ifsc" : "UTIB0000791"
            }
    ]}

so now i want to print only one accountnumber and ifsc code that beleongs "ranisingh@xyz".

profesor79 · Accepted Answer · 2016-04-18T17:19:13.783

1

you can use $slice to get first element from an array:

 db.users.find(
  {"linkedaccounts.virtualid": "ranik@xyz"} ,
  {
    _id: 0,     
   linkedaccounts: {$elemMatch: {virtualid:"ranik@xyz"}}
   }
);

but to reshape document you need to use aggregation like this:

   db.users.aggregate([
   {$unwind:"$linkedaccounts"},
   {$match:{"linkedaccounts.virtualid":"ranik@xyz"}},
   {$project:{
       _id:0,
       linkedaccounts:
            {accountnumber:"$linkedaccounts.accountnumber",
                      ifsc:"$linkedaccounts.ifsc"}       
       }}
   ])

edited Apr 18 '16 at 17:19

answered Apr 18 '16 at 10:39

profesor79

9,213
3
31
52

No effect , same result sir. @profesor79 – Satwinder Singh Apr 18 '16 at 10:46
@SunnyDhiman hi - thx for feedback - just realized that there were extra {} - now fixed – profesor79 Apr 18 '16 at 10:57
@profesor79 I don't think this will help - we don't want the first item in the array, we want whichever item matches the query condition. – Vince Bowdren Apr 18 '16 at 11:22
the OP ask only ONE and there is no other specs for that – profesor79 Apr 18 '16 at 11:28
hi @profesor79 thnks for query- but it always provide first accountnumber and ifsc ,my requirement is i want to get data according to virtual id which i provide in " " like ranisingh@xyz. – Satwinder Singh Apr 18 '16 at 11:35
@SunnyDhiman - yes - |I overlooked this requirement and even sad that there was no such requirement - apologies for that! fixed now. – profesor79 Apr 18 '16 at 11:45
@profesor79 this query printed whole array , i want just accountnumber and ifsc code . that matches virtual id. – Satwinder Singh Apr 18 '16 at 11:58
@SunnyDhiman thee is aggregation example – profesor79 Apr 18 '16 at 17:19
@profesor79 Thanks a lot , this is what exactly i need... thanks once again..!!!! – Satwinder Singh Apr 19 '16 at 05:21

Vince Bowdren · Answer 2 · 2016-04-18T11:46:37.483

-2

You can use the "positional $ operator" to make it return only the matching sub-document, instead of all sub-documents in the array. Try this:

db.users.find(
  {"linkedaccounts.virtualid": "ranik@xyz"} ,
  {
    _id: 0, 
    'linkedaccounts.$.accountnumber': 1,
    'linkedaccounts.$.ifsc': 1
   }
);

That should return you only the matching sub-document, like this:

{
  _id: ObjectId("564b16849408a50b00ec235e"),
  "linkedaccounts" : [
    {
      "accountnumber" : "07916956560873",
      "ifsc" : "UTIB0000791"
    }
  ]
}

Ref: https://docs.mongodb.org/manual/reference/operator/projection/positional/#proj.S

Update in reply to comments: this does not work, because this type of query is limited to only one use of the positional $ operator. I recommend using the aggregation pipeline instead.

edited Apr 18 '16 at 11:46

answered Apr 18 '16 at 11:21

Vince Bowdren

8,326
3
31
56

"errmsg" : "Cannot specify more than one positional proj. per query.", – profesor79 Apr 18 '16 at 11:23
Ah yes, that is a limitation. I think you might need to use the greater power of the aggregation pipeline instead to get exactly what you are after. – Vince Bowdren Apr 18 '16 at 11:25
there is no need for aggregation - my query is sufficient ;-) - pls try it – profesor79 Apr 18 '16 at 11:26
hi vincebowdren there is error: Error: error: { "waitedMS" : NumberLong(0), "ok" : 0, "errmsg" : "Positional projection 'linkedaccount.$.accountnumber' does n ot match the query document.", "code" : 2 – Satwinder Singh Apr 18 '16 at 11:31
@SunnyDhiman - this answer will not work; as professor79 has mentioned, this type of projection has a limitation that you cannot use more than one positional projector per query. But I think the error you are getting is due to a typo: "linkedaccount" instead of "linkedaccounts". – Vince Bowdren Apr 18 '16 at 11:36
@vincebowdren now i using only one positional projector as you mention to get only accountnumber but in result whole linkedaccount array printed. – Satwinder Singh Apr 18 '16 at 11:44
Query: db.users.find( {"linkedaccounts.virtualid": "ranik@xyz"} , { _id: 0, 'linkedaccounts.$.accountnumber': 1 } ) – Satwinder Singh Apr 18 '16 at 11:46
@vincebowdren result: "linkedaccounts" : [ { "accountname" : "Rani K Singh", "accountnumber" : "07916956560873", "virtualid" : "ranik@handypay", "_id" : "571488b9720023940539959f", "ifsc" : "UTIB0000791", "isDefaultReceiving" : true, "isDefaultFunding" : false, "isAppEnabled" : true, "aeba" : false, "accountsource" : 0 } ] – Satwinder Singh Apr 18 '16 at 11:47
@SunnyDhiman Sorry, it won't work: I was wrong about how much use you could get out of the positional $ operator. – Vince Bowdren Apr 18 '16 at 11:50

MongoDB: Retrieving only specific record

2 Answers2