Array indexes not getting invoked

SQL++
1

Hi team ,

I created indexes like

CREATE INDEX Last_nm_idx18 on csrt
(ALL DISTINCT array substr(Lower(lst_nm),i,4) for i in array_range(0,length(Lower(lst_nm))-3,1) end)
where meta().id like ‘CX%’ USING GSI;

what this essentially does is , It takes the last name field . breaks it and indexes it
example
Hemanth will be
[ hema, eman , mant ,anth ]

this will be indexed as as array index .

But when i try to write a query that invokes it the indexes is not getting invoked .

The below SQL is invoking only primary indexes .

select * from
csrt
where
any v in array substr(Lower(lst_nm),i,4) for i in array_range(0,length(Lower(lst_nm))-3,1) end SATISFIES v = ‘dill’ end
and meta().id like ‘CX%’ ;

2 
CREATE INDEX Last_nm_idx18 ON csrt
(ALL DISTINCT ARRAY SUBSTR(LOWER(lst_nm),i,4) FOR i IN ARRAY_RANGE(0,LENGTH(lst_nm)-3,1) END)
WHERE META().id LIKE "CX%" USING GSI;

SELECT *
FROM csrt
WHERE META().id LIKE "CX%"
      AND ANY i IN ARRAY_RANGE(0,LENGTH(lst_nm)-3,1) SATISFIES SUBSTR(LOWER(lst_nm),i,4) = "dill" END;

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

3

Nope that doesn’t invoke the indexes either
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan2”,
“index”: “csrt_primary_index”,
“index_id”: “4918a04554d5b517”,
“keyspace”: “csrt”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““CY””,
“inclusion”: 1,
“low”: ““CX””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Fetch”,
“keyspace”: “csrt”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((meta(csrt).id) like “CX%”) and any v in array_range(0, (length((csrt.lst_nm)) - 3), 1) satisfies (substr0(lower((csrt.lst_nm)), (csrt.i), 4) = “dill”) end)”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT *\r\nFROM csrt\r\nWHERE META().id LIKE “CX%”\r\n AND ANY v IN ARRAY_RANGE(0,LENGTH(lst_nm)-3,1) SATISFIES SUBSTR(LOWER(lst_nm),i,4) = “dill” END;”
}

4

Updated previous post. Try again.

ANY v ===> ANY i

5

This works great . . . Thanks