Cloudant Selector Query for fetching particular elements inside Array

Question

I have the below requirement. In the below array elements I have to select and compare the value of LoanAmount. In the previous posts, the below solutions are mentioned.

{
  "_id": "65c5e4c917781f7365f4d814f6e1665f",
  "_rev": "2-73615006996721fef9507c2d1dacd184",
  "userprofile": {
            "name": "tom",
            "age": 30,
            "employer": "Microsoft"            
                 },
  "loansBorrowed": [{"loanamount": 5000,
                     "loandate": "01/01/2001",
                     "repaymentdate": "01/01/2001",
                     "rateofinterest": 5.6,
                     "activeStatus": true,
                     "penalty": {
                        "penalty-amount": 500,
                        "reasonforPenalty": "Exceeded the date by 10 days"        
                     }
                    },
                    {
                      "loanamount": 3000,
                      "loandate": "01/01/2001",
                      "repaymentdate": "01/01/2001",
                      "rateofinterest": 5.6,
                      "activeStatus": true,
                      "penalty": {
                        "penalty-amount": 400,
                        "reasonforPenalty": "Exceeded the date by 10 days"
                      }
                    },
                    {
                      "loanamount": 2000,
                      "loandate": "01/01/2001",
                      "repaymentdate": "01/01/2001",
                      "rateofinterest": 5.6,
                      "activeStatus": true,
                      "penalty": {
                        "penalty-amount": 500,
                        "reasonforPenalty": "Exceeded the date by 10 days"
                      }
                    }
                  ]
                }

Index:
     { 
      "index": {
                "fields": [{
                    "name": "loansBorrowed.[].loanamount",
                    "type":"number"            
                }],            
                "type": "json"
            }

Selector query:

{"selector": {
        "loansBorrowed": {
            "$elemMatch": {
                "loanamount": 3000
            }
        }
    }
}

But that index and selector queries are providing all the records for that particular Query instead of providing me only record with 3000. Please suggest how to fetch only particular element inside an array block.

For every record returned, are you sure that there is no subloans element that contains the loadamout of 3000? $elemMatch doesnt retuirns the array that matched, it returned the whole document. — Alexis Côté, Aug 02 '17 at 14:09
A very precise dupe: https://stackoverflow.com/questions/33262573/cloudant-selector-query — xpqz, Aug 02 '17 at 14:22
Sort of. In this case the OP is specifically asking about only returning a specific element in the array. That was also asked in the other post, but not really answered. — markwatsonatx, Aug 02 '17 at 14:38

markwatsonatx · Answer 1 · 2017-08-02T14:43:24.817

I don't think it's possible to only return specific items in an array. You could accomplish something similar using views. Here is an example design document:

{
  "_id": "_design/loans",
  "_rev": "1-a115abe01632dd43ee1d0d10546b737d",
  "views": {
    "by_amount": {
      "map": "function (doc) {\n  if (doc.loansBorrowed) {\n    for (var i=0; i<doc.loansBorrowed.length; i++) {\n      emit(doc.loansBorrowed[i].loanamount, {userprofile: doc.userprofile, loan:doc.loansBorrowed[i]});\n    }\n  }\n}"
    }
  },
  "language": "javascript"
}

This creates a view called by_amount. Here is the map function:

function (doc) {
  if (doc.loansBorrowed) {
    for (var i=0; i<doc.loansBorrowed.length; i++) {
      emit(doc.loansBorrowed[i].loanamount, {userprofile: doc.userprofile, loan:doc.loansBorrowed[i]});
    }
  }
}

Here I am using the loan amount as the key. This let's you query by the loan amount. The value can be whatever you want to return. In this case I am returning a document with the user's profile and the loan.

You can then query this view like so:

https://xxx.cloudant.com/YOUR_DB/_design/loans/_view/by_amount?key=3000

Which results in the something like the following (note: I added a second loan with a value of 3000 to show how it would look with multiple loans that matched):

{
   "total_rows":6,
   "offset":2,
   "rows":[
      {
         "id":"796a8954600cee9dbb9e0a4040593942",
         "key":3000,
         "value":{
            "userprofile":{
               "name":"tom",
               "age":30,
               "employer":"Microsoft"
            },
            "loan":{
               "loanamount":3000,
               "loandate":"01/01/2001",
               "repaymentdate":"01/01/2001",
               "rateofinterest":5.6,
               "activeStatus":true,
               "penalty":{
                  "penalty-amount":400,
                  "reasonforPenalty":"Exceeded the date by 10 days"
               }
            }
         }
      },
      {
         "id":"c93f52da36a51f0ddd75f5be381c916e",
         "key":3000,
         "value":{
            "userprofile":{
               "name":"joe",
               "age":50,
               "employer":"Google"
            },
            "loan":{
               "loanamount":3000,
               "loandate":"01/01/2001",
               "repaymentdate":"01/01/2001",
               "rateofinterest":5.6,
               "activeStatus":true,
               "penalty":{
                  "penalty-amount":400,
                  "reasonforPenalty":"Exceeded the date by 10 days"
               }
            }
         }
      }
   ]
}

Cloudant Selector Query for fetching particular elements inside Array

1 Answers1

Linked