CouchBase Index nested elements

Question

In CouchBase(4.1.1) N1QL can index get created using an entire element take these two example.

say we have this document structure:

{
   "name": "blah",
   "credentials": [
       {
          "level": 5,
          "sector": "XP"
       }
   ],
}

Now say we wanted to create index1 based off of name and entire credentials element is that possible?

something like

create index indexName on `bucketName` (name, credentials) USING GSI;

or index2 based off of name and one of the nested fields like level; how could this be done? something like

create index indexName on `bucketName`(name, credential.levels) USING GSI; 

when running explain my secondary indexes are not being used and couchbase defaults to the primary index for this bucket.

here is the select that I am using.

select s.name, s.credentials 
from `security` unnest s.credentials
where credentials is not missing and name = 'tom';

Here is the produced explain:

{
    "requestID": "f8d46eeb-3898-4ace-a24f-1582e0504eb7",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "PrimaryScan",
                    "index": "#primary",
                    "keyspace": "read",
                    "namespace": "default",
                    "using": "gsi"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "as": "s",
                                "keyspace": "bucketName",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Unnest",
                                "as": "beacons",
                                "expr": "(`s`.`credentials`)"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((`s`.`name`) = \"tom\")"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "(`s`.`id`)"
                                    },
                                    {
                                        "expr": "`credentials`"
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.82063ms",
        "executionTime": "2.765439ms",
        "resultCount": 1,
        "resultSize": 1917
    }
}

Answer 1

Based on my findings if you are attempting to create an index you must exclude the documents that do not contain the field you are indexing on. So for my example above once I added: name is not missing inside of the where statement my query began using my secondary index.

The blurb below from the Couchbase site led me to this finding

Attention: MISSING items are not indexed by indexers. To take advantage of covering indexes and for the index to qualify, a query needs to exclude documents where the index key expression evaluates to MISSING.

Answer 2

The following queries should use your index.

select s.name, s.credentials 
from `security` s
where s.credentials is not missing and s.name = 'tom';

select s.name, credentials 
from `security` s unnest s.credentials
where s.credentials is not missing and s.name = 'tom';