Help for Array and like uses together

ANY needs to be used In predicate. Are you trying to find if the firstname is matched or lastname matched?

@vsr1 also i can find it :slight_smile: present problem is different :slight_smile: now go one by one here your query thats my using

(ANY v IN [“owner%”,“d%”] SATISFIES LOWER(db.fname) LIKE v END) AS lname_matched FROM (SELECT RAW META().id FROM db USE INDEX(ix10) WHERE type = “P” AND ANY z IN [LOWER(fname),LOWER(lname)] SATISFIES (ANY v IN [“owner%”,“d%”] SATISFIES z LIKE v END) END) AS q JOIN db ON KEYS q;

We talked about the problem of this query previously.

Now i want to add new some fields for this query.these fields are the sub-document of the main document As in the above example.

how i can do it ?

CRATE INDEX ix11 ON db(type, LOWER(fname));
CRATE INDEX ix12 ON db(type, LOWER(main.ls.name));
CRATE INDEX ix13 ON db(type, ARRAY LOWER(u.name)  FOR u IN uns END);
SELECT *, true fname_matched FROM db WHERE type = "P" AND (LOWER(fname) LIKE "owner%" OR LOWER(fname) LIKE "d%")
UNION ALL
SELECT *, true lname_matched FROM db WHERE type = "P" AND (LOWER(main.ls.name) LIKE "owner%" OR LOWER(main.ls.name) LIKE "d%")
UNION ALL
SELECT *, true uns_matched FROM db WHERE type = "P" AND ANY u IN uns SATISFIES LOWER(u.name) LIKE "owner%" OR LOWER(u.name) LIKE "d%" END ;

so we should separate it okey thank you :slight_smile:

@vsr1
im here again :slight_smile:
this question is different,
if i search “%d%” (only 1 char),system response time is about 1.5s
but if want to search “%da%” (more than 1 char or word),it going to up about 10s
whats the problem ?

When you have leading %, predicate can’t be pushed to indexer need to do complete indexscan, on top you are selecting whole document which requires fetch and discard. Also more than 1 character means more items qualified and data transfer is more.

Check https://dzone.com/articles/a-couchbase-index-technique-for-like-predicates-wi see if you can use this technique.

@vsr1
i cant this technique with array field
here index
create index suffixes_uns_name on test(
ARRAY (DISTINCT ARRAY array_element FOR array_element IN SUFFIXES(LOWER(u.name)) END) FOR u IN uns END
)
WHERE type = “P” and deleted=false and not lid="";

here query
explain select meta().id,test.* from test
where
ANY u IN uns SATISFIES ANY array_element IN SUFFIXES(LOWER(u.name)) SATISFIES array_element LIKE ‘bur%’ END end

and type=“P” and deleted=false and not lid="" limit 20 ;

this is result

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “primary”,
“keyspace”: “test”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “test”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((any u in (test.uns) satisfies any array_element in suffixes(lower((u.name))) satisfies (array_element like “bur%”) end end and ((test.type) = “P”)) and ((test.deleted) = false)) and (not ((test.lid) = “”)))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(meta(test).id)”
},
{
“expr”: “test”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “20”
}
]
},
“text”: “select meta().id,test.* from test\r\nwhere\r\nANY u IN uns SATISFIES ANY array_element IN SUFFIXES(LOWER(u.name)) SATISFIES array_element LIKE ‘bur%’ END end\r\n \r\nand type=“P” and deleted=false and not lid=”" limit 20 ;"
}
]

how i can use this tecnique for array field ?

DISTINCT keyword is missing.

create index suffixes_uns_name on test( DISTINCT ARRAY 
        (DISTINCT ARRAY array_element FOR array_element IN SUFFIXES(LOWER(u.name)) END)
                 FOR u IN uns END)
         WHERE type = "P" and deleted=false and not lid="";

i cant use 2 distinct word in same index query also i solve it

create index suffixes_test on test(deleted,lid,
DISTINCT ARRAY array_element FOR array_element IN SUFFIXES(LOWER(test)) END)
WHERE type = “P”;

select meta().id,test.* from test
where
ANY u IN uns SATISFIES ANY array_element IN SUFFIXES(LOWER(u.name)) SATISFIES array_element LIKE ‘b%’ END end
and type=“P” and deleted=false and not lid="" limit 20 ;

i think according to link it run a little bit slow but i can use index every field.

I have already provided the right index. You can’t use more than one array index keys. But in this case you have only one but it is nested array.

Your new index runs slow because it is not using any array index (i.e not pushing array predicate to index). https://dzone.com/articles/understanding-index-scans-in-couchbase-n1ql-query Explains how indexscan works

@vsr1

select meta().id,fname,true fname_match from test
where
ANY array_element IN SUFFIXES(LOWER(fname)) SATISFIES array_element LIKE “bo%” END
and type="P"
union all
select meta().id,fname,true lname_match from test
where
ANY array_element IN SUFFIXES(LOWER(lname)) SATISFIES array_element LIKE “bo%” END
and type=“P”

im using this query now :slight_smile: but it s not distinct i think they are different json document ,i want them to be distinct by id
i used just union but it didint work

UNION is applied on whole projection. What version of Couchbase you are using.

You are running version 4.6.2-3905 Enterprise Edition (build-3905).

SELECT META().id,fname, lname, CASE WHEN LOWER(fname) LIKE "%bo%" THEN "fname" ELSE "lname" END AS match FROM test
WHERE (type = "P" AND ANY array_element IN SUFFIXES(LOWER(fname)) SATISFIES array_element LIKE "bo%" END)  OR
      (type = "P" AND ANY array_element IN SUFFIXES(LOWER(lname)) SATISFIES array_element LIKE "bo%" END) ;

OR

SELECT RAW {META().id,fname, lname, "match": CASE WHEN LOWER(fname) LIKE "%bo%" THEN "fname" ELSE "lname" END} FROM test
WHERE (type = "P" AND ANY array_element IN SUFFIXES(LOWER(fname)) SATISFIES array_element LIKE "bo%" END)  OR
      (type = "P" AND ANY array_element IN SUFFIXES(LOWER(lname)) SATISFIES array_element LIKE "bo%" END) ;

Check out above query. It should have used UnionScan on both the indexes.