Hi,
We are trying to retrieve data from about couple of million documents which has complicated structure of nested data.
For example the document looks as below
OuterData {
id : outerID
records: [
{
parameters: {
param1:val1,
param2:val2
},
baseAmount : [
{
amount : $ 5,
validity :
},
{
amount : $ 10,
validity :
},
… repeats of parameters array
]
}
]
}
There can be request to fetch an Valid Amount matching set of parameter values for a particular OuterID. The request can contain multiple OuterIDs.
Currently our N1ql query ( using Java SDK ) consists of multiple UNNEST along with Key of the Outer Document itself.
something like this…
select from bucket use keys ( ) UNNEST parameters as param UNNEST params.baseAmount as amount where amount is valid for current Time;
Since there can be multiple requests on Outer IDs , we used union all clause to combine all of these N1ql to minimize I/O operations. The performance on the query ( say 10 unions ) has not been that good ( ~ 40 ms ), even though the document is fetched using key scan.
Can you please suggest is there any more room for improvement on the above query on top of KeyScan . Also what is the suggested approach when retrieving results for such document structure , should we use N1ql or sdk functions to fetch the appropriate data. I really do not want to fetch the document using key in memory and perform such complicated filtering , that would defeat the purpose of N1ql.
Thanks,
Mahesh