N1ql array index

SQL++
, ,
1

How to create index on array of array attribute .I have doc of type invoice in which i have sub array of invoice_services under which i have an other array item_list .Now i need to create index on item_list arrays keys.

MY QUERY

SELECT ins_list.price,ins_list.item_qty,ins_list.item_detail_charges
FROM stitchit_data_bucket invoice
UNNEST invoice.invoice_services as ins UNNEST ins.item_list as ins_list
WHERE invoice.type = ‘invoice’ AND invoice.date
BETWEEN ‘2020-03-01’ AND ‘2020-03-30’ AND invoice.store_id = ‘3221’

My Doc

{
“id”: “00602000000000”,
“date”: “2020-05-06”,
“time_entrance”: “12:00”,
“due_date”: “2020-05-06”,
“due_time”: “12:00”,
“canada_exempt”: “”,
“province_exempt”: “”,
“exempt_1”: “”,
“exempt_2”: “”,
“discount”: “0”,
“discount_method”: “”,
“discount_id”: “0”,
“description”: “”,
“cus_id”: “024420000000”,
“total_bill”: 40,
“total_paid”: 40,
“total_express”: 0,
“total_qty”: 1,
“store_id”: “60”,
“employee_id”: “7019”,
“price_list_id”: “3221190011”,
“referal_or_corp”: “Walk in”,
“referal_or_corp_id”: “0”,
“corp_emp”: “0”,
“corp_po”: “0”,
“corp_req”: “0”,
“status”: “IN”,
“tax_method”: “%”,
“tax_amount”: 0,
“tax_name”: “GST”,
“payment_method”: “Cash”,
“customer_signaure”: “”,
“pickup_name”: “”,
“defect_reason”: “”,
“total_tendered”: 40,
“total_change”: 0,
“invoice_services”: [
{
“id”: “0060200000000000”,
“inv_id”: “00602000000000”,
“date”: “2020-05-06”,
“service_id”: “3221190002”,
“service_name”: “Skirt”,
“start_time”: “”,
“start_date”: “”,
“item_list”: [
{
“sales_item_id”: “006020000000000000”,
“item_id”: “3221190658”,
“item_name”: "Over 32 inches ",
“item_qty”: 1,
“price”: 25,
“tag_3”: "( 3 x ) Shorten : Extra row of Stitching $15 | ",
“item_detail_charges”: 15
}
]
}
],
“type”: “invoice”
}

2

Based on your query you are not required any array index. The following is right index. You can use https://index-advisor.couchbase.com/indexadvisor/#1

CREATE INDEX ix1 ON  stitchit_data_bucket (store_id ,date  ) 
WHERE  type = "invoice"

Check example 4 on query and index on nested arrays
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html#examples

3

from the example you mentioned i created this index but its not calling
my other indexes on different types are calling but this part of my query is not getting scanned by index

ON THIS QUERY
SELECT ins_list.price,ins_list.item_qty,ins_list.item_detail_charges
FROM stitchit_data_bucket invoice
UNNEST invoice.invoice_services as ins UNNEST ins.item_list as ins_list
WHERE invoice.type = ‘invoice’

CREATE INDEX idx_nested ON stitchit_data_bucket
(DISTINCT ARRAY
(DISTINCT ARRAY {ins_list.price,ins_list.item_qty,ins_list.item_detail_charges}
FOR ins_list IN ins.item_list END)
FOR ins IN invoice_services END)
WHERE type = “invoice”;

4

Array indexing can only index single value . You don’t have predicate on array index key. Checkout https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-arrays.html