Array index not working

SQL++
1

I am trying to create a index on nested array invoice_services but its not scanning the correct index.

here is my array index that i created on

create index def_index_inv_services on stitchit_data_bucket (DISTINCT ARRAY ins.service_id FOR v WITHIN invoice_services END);

My N1ql Query

SELECT service.name
FROM stitchit_data_bucket invoice UNNEST invoice.invoice_services as ins
JOIN stitchit_initialization_hq service ON service.id = ins.service_id WHERE service.type = ‘service’
AND invoice.store_id = ‘3221’ AND invoice.type = ‘invoice’

My doc

{

“id”: “00602000000000”,
“date”: “2020-02-14”,
“time_entrance”: “05:57:35”,
“invoice_services”: [
{
“id”: “0060200000000000”,
“inv_id”: “00602000000000”,
“date”: “2020-02-14”,
“service_id”: “3221190019”,
“service_name”: “Pants”,
“due_time”: “”,
“total_bill”: 18,
“check_status”: “IN”,
“working_status”: “rack”,
“check_redo”: “no”,
“created_emp_id”: “1305”,
“store_id”: “3221”,
“completed_time”: “”,
“completed_date”: “”,
“start_time”: “”,
“start_date”: “”,
“item_list”: [
{
“sales_item_id”: “006020000000000000”,
“item_id”: “3221190656”,
“item_name”: "Super Crease ",
“item_qty”: 1,
“price”: 9,
“tag_3”: “”,
“item_detail_charges”: 0
}
],
“type”: “invoice”
}

2

Change WITHIN to IN in the index.

3

Here is my index with changes you said

create index def_index_inv_services on stitchit_data_bucket (DISTINCT ARRAY ins.service_id FOR v IN invoice_services END);

it is scanning my other indexes not the this one here is the list of indexes its scanning
stitchit_data_bucket.def_actual_sale_no_express
stitchit_data_bucket.cash_register_payment

here is the plan text

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IntersectScan",
        "scans": [
          {
            "#operator": "IndexScan3",
            "as": "invoice",
            "index": "def_actual_sale_no_express",
            "index_id": "12af0f7b380004f9",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "stitchit_data_bucket",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"invoice\"",
                    "inclusion": 3,
                    "low": "\"invoice\""
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "IndexScan3",
            "as": "invoice",
            "index": "cash_register_payment",
            "index_id": "6306044bcb82ea37",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "stitchit_data_bucket",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"3221\"",
                    "inclusion": 3,
                    "low": "\"3221\""
                  }
                ]
              }
            ],
            "using": "gsi"
          }
        ]
      },
      {
        "#operator": "Fetch",
        "as": "invoice",
        "keyspace": "stitchit_data_bucket",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Unnest",
              "as": "ins",
              "expr": "(`invoice`.`invoice_services`)"
            }
          ]
        }
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "NestedLoopJoin",
              "alias": "service",
              "on_clause": "(cover ((`service`.`id`)) = (`ins`.`service_id`))",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "IndexScan3",
                    "as": "service",
                    "covers": [
                      "cover ((`service`.`id`))",
                      "cover ((`service`.`name`))",
                      "cover ((`service`.`picture`))",
                      "cover ((`service`.`status`))",
                      "cover ((meta(`service`).`id`))"
                    ],
                    "filter_covers": {
                      "cover ((`service`.`type`))": "service"
                    },
                    "index": "def_service_details",
                    "index_id": "92af9f8cade3ad97",
                    "index_projection": {
                      "entry_keys": [
                        0,
                        1
                      ],
                      "primary_key": true
                    },
                    "keyspace": "stitchit_initialization_hq",
                    "namespace": "default",
                    "nested_loop": true,
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "(`ins`.`service_id`)",
                            "inclusion": 3,
                            "low": "(`ins`.`service_id`)"
                          }
                        ]
                      }
                    ],
                    "using": "gsi"
                  }
                ]
              }
            },
            {
              "#operator": "Filter",
              "condition": "(((cover ((`service`.`type`)) = \"service\") and ((`invoice`.`store_id`) = \"3221\")) and ((`invoice`.`type`) = \"invoice\"))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "cover ((`service`.`name`))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT service.name\r\nFROM stitchit_data_bucket invoice UNNEST invoice.invoice_services as ins\r\nJOIN stitchit_initialization_hq service ON service.id = ins.service_id WHERE service.type = \"service\"\r\nAND invoice.store_id = \"3221\" AND invoice.type = \"invoice\""
}
4

Change v to ins

create index def_index_inv_services on stitchit_data_bucket (DISTINCT ARRAY ins.service_id FOR ins IN invoice_services END);

5

Hi thanks :slightly_smiling_face: but the result is same it is not scanning the def_index_inv_services index

here the query

SELECT service.name
FROM stitchit_data_bucket invoice UNNEST invoice.invoice_services as ins
JOIN stitchit_initialization_hq service ON service.id = ins.service_id WHERE service.type = “service”
AND invoice.store_id = “3221” AND invoice.type = “invoice”

6

It is using the right index. You can avoid InterSectScan by providing HINT like below

SELECT service.name
FROM stitchit_data_bucket AS invoice  USE INDEX(cash_register_payment)
UNNEST invoice.invoice_services AS ins
JOIN stitchit_initialization_hq AS service ON service.id = ins.service_id
WHERE service.type = "service" AND invoice.store_id = "3221" AND invoice.type = "invoice";

Hope your cash_register_payment has following definition.

CREATE INDEX cash_register_payment ON stitchit_data_bucket(store_id,......) WHERE type = "invoice";

The def_index_inv_services will not qualify and not usefull in your case as there are no constant predicate (join predicate) on array index key

Checkout https://blog.couchbase.com/ansi-join-support-n1ql/