vinSan vinSan - 5 months ago 27
JSON Question

Cloudant Selector Query

I would like to query using cloudant db using selector, for example that is shown below: user would like to have loanborrowed whose amount exceeds a number, how to access the array in a cloudant selector to find a specific record

{
"_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"
}
}
]
}

Answer

If you use the default Cloudant Query index (type text, index everything):

{
   "index": {},
   "type": "text"
}

Then the following query selector should work to find e.g. all documents with a loanamount > 1000:

"loansBorrowed": { "$elemMatch": { "loanamount": { "$gt": 1000 } } }

I'm not sure that you can coax Cloudant Query to only index nested fields within an array so, if you don't need the flexibility of the "index everything" approach, you're probably better off creating a Cloudant Search index which indexes just the specific fields you need.

Comments