Data loading in N1ql

SQL++
,
13

Try this. No use of creating Array index because you don’t have predicate.

CREATE INDEX cust_ix1 ON stitchit_initialization_hq(customer_id, last_name, first_name) WHERE type ="customer";
CREATE INDEX inv_ix1 ON stitchit_initialization_hq(store_id, due_date) WHERE type ="invoice";

SELECT d.*, customer.first_name, customer.last_name
FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill,
             ins.due_time AS inv_time, ins.due_date AS inv_date ,ins.id AS sales_id
      FROM stitchit_data_bucket AS inv USE INDEX (inv_ix1)
      UNNEST inv.invoice_services AS ins
      WHERE inv.type = "invoice" AND inv.due_date BETWEEN "2020-03-29" AND "2020-04-04" AND inv.store_id = "3221"
            AND ins.working_status = "rack" AND ins.express = "express" ) AS d
INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = d.cus_id AND customer.type = "customer"
ORDER BY d.inv_time ASC;

OR

CREATE INDEX cust_ix1 ON stitchit_initialization_hq(customer_id, last_name, first_name) WHERE type ="customer";
CREATE INDEX inv_ix2 ON stitchit_initialization_hq(store_id,
             DISTINCT ARRAY [ins.working_status, ins.express] FOR ins IN invoice_services END, due_date) WHERE type ="invoice";

SELECT d.invoice_id, d.cus_id, d.total_bill, d.first_name, d.last_name, ins.*
FROM (SELECT inv.id AS invoice_id, inv.cus_id, inv.total_bill,
             customer.first_name, customer.last_name,
             ARRAY {"inv_time":ins.due_time, "inv_date": ins.due_date, "sales_id": ins.id}
               FOR ins IN inv.invoice_services WHEN  ins.working_status = "rack" AND ins.express = "express" END AS invoice_services
      FROM stitchit_data_bucket AS inv USE INDEX (inv_ix2)
      INNER JOIN stitchit_initialization_hq AS customer ON customer.customer_id = inv.cus_id AND customer.type = "customer"
      WHERE inv.type = "invoice" AND inv.due_date BETWEEN "2020-03-29" AND "2020-04-04" AND inv.store_id = "3221"
            AND ANY ins IN inv.invoice_services SATISFIES [ins.working_status,ins.express] = ["rack","express"] END) AS d
      )
UNNEST d.invoice_services AS ins
ORDER BY ins.inv_time;
1 Like