N1QL Union performance with UNNEST

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

Please post complete exact query so that we can check any alternatives to unnest is available. Is parameters is Array (in sample document it is not).

Why are using UNION ALL if you are using keyscan . You can provide array of keys IN USE KEYS.

SELECT … FROM bucket USE KEYS [“key1”,“key2”,“key3”,…]

If you already know the keys and able to process desired predicates you can also try SDK to fetch directly and process and see if you get better latency.

For security reasons i cannot post the exact query , i am sorry about that. Parameters are not array , but record is which contains list of param:values and its associated prices.
The reason to use Union ALL is because we have a complicated hierarchy of prices in our output structure and we have created an unique alias to each individual result and storing the same result in Map and pushing in back in our output hierarchy…

Using the SDK would be our next step , but as i mentioned above , this really beats the purpose of using N1QL if there is such a difference. I would like to better understand the limitations of N1QL before i go with the SDK options using keys

Understand that. In that case we can suggest generically because don’t know lot of information.

If parameters is not array why are you doing UNNEST parameters as param? You can avoid it. If you are not projecting UNNEST values you can check ANY syntax.
There is no limitations on N1QL, In your case you are not doing IndexScan and using KeyScan (data needs two hops) by directly fetching you have one hop but all processing of data needs in SDK.
Also if you executing this query repeatedly set adhoc=flase, If using 4.5.1 or above set pretty=false

Sorry about that , i meant the UNNEST is on records array and not params , Updated the query to reflect more accurately what we are doing.
Also , we are using 4.5.0 with adhoc false set already

Are you projecting values from rec or amount

Amount and also some values from OuterDocument like Id

If you need amount you need to do UNNEST. don’t see any further improvement.

Thanks for your prompt responses