N1QL - Indexes when using tokens to search in array field

I am trying to use tokens function to search lastname or firstname in names array since we don’t enable FTS.

This is sample document:
[
{
“id”: “1111”,
“t”: {
“_creationTime”: 1585766059,
“_type”: “Person”,
“birthdate”: “19000101”,
“names”: [
{
“family”: “ftest1”,
“given”: “gtest1”,
“prefix”: null,
“suffix”: null,
}
]
},
{
“id”: “2222”,
“t”: {
“_creationTime”: 1608279352,
“_type”: “Person”,
“birthdate”: “19000101”,
“names”: [
{
“family”: “ftest2”,
“given”: “gtest2”,
“prefix”: null,
“suffix”: null
}
]
}
]
I am trying to use tokens function to do the pattern search either on family or given name.
How can I create an index and what the query looks like?

Thanks,

Matthew

CREATE   INDEX ix1 ON default( DISTINCT ARRAY  (DISTINCT ARRAY v FOR v IN [n.family, n.given] END) FOR n IN names END) WHERE _type = "Person";

SELECT * 
FROM default 
WHERE _type = "Person" AND ANY n IN names SATISFIES( ANY v IN [n.family, n.given] SATISFIES v LIKE "xyz%" END) END;

Thank you for your response.

If I need to support pattern match, for example n.family like “%xyz%”, what is the best approach w/o enabling FTS and how to create the index and construct the query?

Thanks,

Matthew

N1QL supports n.family like “%xyz%”. prefix is wild card may not able to optimize better.

Other than SUFFIXES function, I am also interesting in tokens function. Can you please give me an example how to use tokens function in my case, and how to create array index with token function?

Thanks,

Matthew

https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/stringfun.html#examples-16

TOKENS() returns array use that create array index, Check example 4

I try to create index on names.family like:

CREATE INDEX idx_fname_token ON t (distinct array v.family for v in tokens(names, {“case”: “lower”, “name”: “false”}) end);

however from index_stats, the total number of indexed items is 0, which means it’s an empty index.

Is anything wrong on this index creation statement?

Thanks,

Matthew

names is ARRAY , TOKENS (names) give ARRAY of string. v no longer object to do v.family

CREATE   INDEX ix1 ON default( DISTINCT ARRAY  (DISTINCT TOKENS(n.family, {"case":"lower"})  FOR n IN names END) WHERE _type = "Person";

SELECT * 
FROM default 
WHERE _type = "Person" AND ANY n IN names SATISFIES( ANY v IN  TOKENS(n.family, {"case":"lower"})   SATISFIES v = "xyz" END) END;

If the index is created as:
CREATE INDEX ix1 ON default( DISTINCT ARRAY (DISTINCT ARRAY v FOR v IN [n.family, n.given] END) FOR n IN names END) WHERE _type = “Person”;

How can I improve sorting performance by using index keys? for example: how can I construct the query order by clause if need to sort it by names.family, names.given and then only return 100 records:

Thanks,

Matthew

Query cannot uses index order because index key order doesn’t match query. It must get all qualified documents do sort and apply limit.

This is my testing showing sorting and limit using index:
index:
CREATE INDEX idx_Referring_lname ON my_bucket (lower(Lastname),lower(Firstname)) WHERE (_type = “Physician” and RefPhy = “YES”)

Query1: using index sorting and limit to 10 records
select Firstname,Lastname from my_bucket
where _type=“Physician” and RefPhy=“YES”
and lower(Lastname) like “c%”
order by lower(Lastname),lower(Firstname) limit 10
Plan chart:

Query2: does not use index sorting:

select Firstname,Lastname from my_bucket
where _type=“Physician” and RefPhy=“YES”
and lower(Lastname) like “c%”
order by Lastname,Firstname limit 10