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?
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;
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?
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?
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:
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: