N1ql 2 step nested indexes

SQL++
1

HI

My Query

SELECT
			inv.date,
			inv.id,
			inv.store_id,
			inv.description,
			inv.cus_id,
			inv.total_after_disc,
			inv.total_bill,
			inv.tax_amount_price,
			inv.employee_id,
			inv.account_type,
			inv.invoice_taxes,
			ins.service_id,
			ins.service_name,
			inst.department_id,
			inst.department_name,
			inst.item_id,
			inst.item_qty,
			inp.terminal_id
			FROM st_data_bucket inv  UNNEST inv.invoice_services AS ins
			UNNEST ins.item_list AS inst
			UNNEST inv.invoice_payment AS inp
			WHERE inv.date between '2022-08-01' AND  '2022-08-31'
			AND  inv.referal_or_corp = 'Walk in'  AND inv.type = 'invoice'

Here i am getting data from 2 levels for the level 1 (invoice_services,invoice_payment) i created 2 indexes but for level 2 (ins.item_list) i dont know how to cover this in indexes

 CREATE INDEX def_net_suite_ref_corp_3 ON `st_data_bucket`(ALL `invoice_services`,ALL `invoice_payment`,`type`,`referal_or_corp`,`date`,`store_id`,`total_bill`,`account_type`,`invoice_taxes`,`tax_amount_price`,`total_after_disc`,`id`,`cus_id`,`employee_id`,`description`) 
	WHERE type = "invoice" 
	
	
		 CREATE INDEX def_net_suite_ref_corp_4 ON `st_data_bucket`(ALL `invoice_payment`) WHERE type = "invoice"

When i see explain in couchbase server these indexes are not covering properly

2

This will not able to cover because index will have too many items and size can be bigger.
Use following index and Fetch the document

CREATE INDEX ix3 ON st_data_bucket(date, referal_or_corp) WHERE type = "invoice";

1 Like
3

Can we add any condition if inv.invoice_payment array is empty then instead of return empty results it should make inp.terminal_id to null or empty and show rest of the result because right now if inv.invoice_payment is empty then its returning nothing

Thanks

4

Use
LEFT UNNEST inv.invoice_payment AS inp

The following different way of representation one per document.

SELECT inv.date, inv.id, inv.store_id, inv.description, inv.cus_id, inv.total_after_disc, inv.total_bill, inv.tax_amount_price,
       inv.employee_id, inv.account_type, inv.invoice_taxes,
       ARRAY { ins.service_id, ins.service_name,
               "item_list": ARRAY { inst.department_id, inst.department_name, inst.item_id, inst.item_qty} FOR inst IN ins.item_list END}
       FOR ins IN inv.invoice_services END invoice_services,
       ARRAY inp.terminal_id FOR inst IN inv.invoice_payment END invoice_payment
FROM st_data_bucket inv
WHERE inv.date between '2022-08-01' AND  '2022-08-31'
      AND  inv.referal_or_corp = 'Walk in'  AND inv.type = 'invoice';
5

Thanks the LEFT UNNEST working fine but i am trying to run the query you mentioned above which throws error of “msg”: “syntax error - at END”, plus there is no bucket reference in this query so we really dont need any bucket ref

Thanks

6

ok i have modified query seems working now but saying missing fields

SELECT inv.date, inv.id, inv.store_id, inv.description, inv.cus_id, inv.total_after_disc, inv.total_bill, inv.tax_amount_price,inv.employee_id, inv.account_type, inv.invoice_taxes,
ARRAY { ins.service_id, ins.service_name, "item_list": ARRAY { inst.department_id, inst.department_name, inst.item_id, inst.item_qty} FOR inst IN ins.item_list END}  FOR ins IN ins.invoice_services END ,
ARRAY inp.terminal_id FOR inp IN inv.invoice_payment END invoice_payment from st_data_bucket inv
WHERE inv.date between '2022-08-01' AND  '2022-08-31' AND  inv.referal_or_corp = 'Walk in'  AND inv.type = 'invoice';
7 
[
    {
        "account_type": "",
        "cus_id": "030122000001",
        "date": "2022-08-01",
        "description": "",
        "employee_id": "335780",
        "id": "03012200000000",
        "invoice_payment": [
            "Manual"
        ],
        "invoice_taxes": [
            {
                "tax_amt": 21.255000000000003,
                "tax_name": "HST",
                "tax_value": 13
            },
            {
                "tax_amt": 0,
                "tax_name": "None",
                "tax_value": 0
            }
        ],
        "store_id": "301",
        "tax_amount_price": 0,
        "total_after_disc": 163.5,
        "total_bill": 184.755
    },
    {
        "account_type": "",
        "cus_id": "030122000001",
        "date": "2022-08-01",
        "description": "",
        "employee_id": "335780",
        "id": "03012200000001",
        "invoice_payment": [
            "Manual"
        ],
        "invoice_taxes": [
            {
                "tax_amt": 10.92,
                "tax_name": "HST",
                "tax_value": 13
            },
            {
                "tax_amt": 0,
                "tax_name": "None",
                "tax_value": 0
            }
        ],
        "store_id": "301",
        "tax_amount_price": 0,
        "total_after_disc": 84,
        "total_bill": 94.92
    },

this is the result

8

inv.invoice_services

or use updated query in previous post

1 Like