CouchBase Index nested elements

1
votes

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
    }
}
2
databaseindexingcouchbasen1qlnosql
Please edit the question to indicate (1) the Couchbase version, (2) the query you want run (include the full query), and (3) what you mean by "these are not working for me" (what is happening?). - geraldss
I just ran those on Couchbase and they executed correctly. I assume that what you mean by "not working for me" is that you are running a SELECT that isn't using those indexes? It would help if you included the SELECT you are using and the EXPLAIN for it (which is probably what @geraldss means) - Matthew Groves
@mgroves I have added the sample query - Marquis Blount
@geraldss this is a mock example but largely mimics the structure of the fields im interested in inside of the actual document. The actual document is pretty large and has a ton of sensitive information so i cant provide the actual query. Hopefully we can still resolve with the mock example - Marquis Blount

2 Answers

2
votes

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.

1
votes

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';