How to create index for UNNEST select query?

SQL++
1

I created many select query with UNNEST. but i am unable to create index .

SELECT SUM(x.tAmount) As tamount FROM test AS c UNNEST c.tRefund AS x WHERE c.type="sales" AND c.tInvoiceId="' . $invoiceId . '" AND c.tRefund IS NOT NULL

index
CREATE INDEX sale1 ON test (tInvoiceId,tRefund[*].tAmount,tRefund) WHERE type="sales" AND tRefund IS NOT NULL;

CREATE INDEX sale12 ON test (tInvoiceId,tRefund.tAmount,tRefund) WHERE type="sales" AND tRefund IS NOT NULL;

is it correct?
thank you.

1 Like
2 
CREATE INDEX sale12 ON test (tInvoiceId, ALL ARRAY tr.tAmount FOR tr IN tRefund END) 
WHERE type="sales";

SELECT SUM(tr.tAmount) As tamount 
FROM test AS c 
UNNEST c.tRefund AS tr 
WHERE c.type="sales" AND c.tInvoiceId="' . $invoiceId . '" AND tr.tAmount IS NOT NULL;

Make sure Unnest Alias tr and Array Index variable are same

2 Likes
3

Thank you @vsr1
How to create index with multiple array?
SELECT x.category.category_name AS catName, x.item_name AS itemName, SUM(x.quantity) AS qty, x.priceSelected.sku AS sku, x.priceSelected.variation AS variation, SUM(x.itemActualTotal) AS itemGross, SUM(r.Amount) AS Amount, SUM(p.tAmount) AS tAmount FROM test AS c UNNEST c.items AS x UNNEST c.tRefund AS r UNNEST c.tPayments AS p WHERE c.type="sales" AND c.owner="' . $username . '" AND c.tRefund[*].tRefundAmount!= "" AND x.quantity > 0 AND x.itemActualTotal >0 GROUP BY x.category.category_name, x.item_name , x.priceSelected.sku,x.priceSelected.variation order by itemGross desc

Index
CREATE INDEX itemCatTxn ON test(owner, tInvoiceId,store_name, _sync.time_saved, ALL ARRAY x.category.category_name FOR x IN items END, ALL ARRAY x.item_name FOR x IN items END, ALL ARRAY x.priceSelected.sku FOR x IN items END, ALL ARRAY x.variation.sku FOR x IN items END, ALL ARRAY x.itemTotal FOR x IN items END, ALL ARRAY x.quantity FOR x IN items END, ALL ARRAY x.itemActualTotal FOR x IN items END, ALL ARRAY p.tAmount FOR p IN tPayments END, ALL ARRAY r.Amount FOR r IN tRefund END, tDateTime ) WHERE type="sales" ;

error

GSI CreateIndex() - cause: Fails to create index. Multiple expressions with ALL are found. Only one array expression is supported per index.

Thank you.

4

Only one array index key is possible. If you need multiple array keys create single array index key and store whole array as scalar. Then pos indexscan filter will applied.

1 Like