hi guys i need help
i have query like a
WHERE type = “P” AND (LOWER(fname) LIKE "?%"
and i have array like a [“abc”,“bdd”] which is from splited sentence
i dont want to create for loop and take every item in array then put in query
i want to give just array
im working on node js and cb 4.6.5
if i can,how ?
thanks
Could you please explain what will be problem.
Example: LOWER(fname) present in the ARRAY return results.
WHERE type = “P” AND LOWER(fname) IN [“abc”,“bdd”]
can you say how “in” function works.is it comparing array item completely or working “like” function cuz i want to give like function an array.
LOWER(fname) IN [“abc”,“bdd”] is same as (LOWER(fname) = “abc” OR LOWER(fname) = “bdd”)
Like can be done on string not on array of strings. You can do this
ANY v IN [“abc”,“bdd”] SATISFIES LOWER(fname) LIKE v END;
If that is not what you are looking, Please post question clearly.
i think i m looking for second one i will try
thanks for helping
WHERE type = “P” AND ANY z IN [LOWER(fname),LOWER(lname)] SATISFIES (ANY v IN [“a%”,“b%”] SATISFIES z LIKE v END) END;
finally i got it.it means WHERE type = “P” AND
(LOWER(fname) like “a%” or LOWER(fname) like “b%” or LOWER(lname) like “a%” or LOWER(lname) like “b%” )
@vsr1 thank you for that
and final question im using index with suffixes
CREATE INDEX idx_fname_suffix1
ON db(DISTINCT ARRAY s FOR s IN SUFFIXES(LOWER(fname)) OR SUFFIXES(LOWER(lname)) END)
WHERE type = “P”;
and i use it
USE INDEX(idx_fname_suffix1) WHERE type = “P” AND ANY z IN [LOWER(fname),LOWER(lname)] SATISFIES (ANY v IN [“a%”,“b%”] SATISFIES z LIKE v END) END;
is it possible to make it simply way or better performance ?
thanks
If you want supply search value as array you need to use different index because array index doesn’t match because of two any clauses one of the any has search value IN [“a%”,“b%”] .
im sorry now 
so what do you recommend for me,can i use more index in a query ?
i hope you understand what i want to do
You never posted complete query. Post complete query and Couchbase version and will recommend best index.
Other option is separate search elements separate instead of array. Is LIKE “a%”, wildcard only on trailing or do you need “%a%”
let me explain
i have some document which is sperate with “type”.
for example
{
fname:“owner”,
lname:“dods”,
…(more area)
type:“P”
}
then i have some words or characters
example
[“owner”,“d”]
its my index query maybe its doesnt work
CREATE INDEX idx_fname_suffix1
ON db(DISTINCT ARRAY s FOR s IN SUFFIXES(LOWER(fname)) OR SUFFIXES(LOWER(lname)) END)
WHERE type = “P”;
and this is my search query
SELECT DISTINCT db.* FROM db USE INDEX(idx_fname_suffix1) WHERE type = “P” AND ANY z IN [LOWER(fname),LOWER(lname)] SATISFIES (ANY v IN [“owner%”,“d%”] SATISFIES z LIKE v END) END;
i want to search on some area (“fname”,“lname”) with like method also i want query can easy developable (maybe i use wrong word) because in future i have to include “more area” for search or more search word([“owner%”,“d%”,"%st%"])
thats my question im sorry for bad language
if there are any easier way or other methods have,please tell me.
thanks
@vsr1
Based on the input you passed requirement the following index and query is right one.
CRATE INDEX ix10 ON db(type,LOWER(fname),LOWER(lname));
SELECT db.* 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;thank you so much
i forget write my db versiyon
Version: 4.6.2-3905 Enterprise Edition (build-3905)
you can write if any problem can be on db version
you are very helpful,thank you again 
have nice day
SELECT db.*, (ANY v IN ["owner%","d%"] SATISFIES LOWER(db.fname) LIKE v END) AS fname_matched, (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;
i read pdf all of night
if i need use like %?% can i use same index or need create suffixes index (i seen it pdf example so i m confused)
sorry about a lot question but im tryinng understand it
thank you
Based on your requirements. Only type = “P” can be pushed indexer and all other predicates applied later. above index will work for your case.
FYI: In N1QL Percent (%) matches any string of zero or more characters; underscore (_) matches any single
character. The wildcards can be escaped by preceding them with a backslash (). Backslash itself can also
be escaped by preceding it with another backslash.
i got it
thank you again
have a nice day
hello again
i try to add new fields my search query, these fields are the sub-document of the main document, one of the field is array
for example
{
fname:“owner”,
main:{
ls:{
name:“name”,
enddate:“2018”
},
uns:[
{
name:“name1”,
enddate:“2015”
},
{name:“name2”,
enddate:“2019”
}
]
}
}
maybe i can use unnest funciton but i have 2 field in sub document i have some idea
lower(main.ls.name),lower(main.ls.enddate),lower(main.uns[ * ].name),lower(main.uns[ * ].enddate)
or maybe i must use any and in function but this time there will be 3 functions inside
how i can do it ?
thank you
To match any name any where in the document . Try WITHIN.
INSERT INTO default VALUES("001", { "fname":"owner", "main":{ "ls":{ "name":"name", "enddate":"2018" }, "uns":[ { "name":"name1","enddate":"2015" }, {"name":"name2", "enddate":"2019" } ] } });
SELECT META(d).id FROM default d WHERE ANY v WITHIN d SATISFIES v.name = "name1" END;@vsr1
as you know, I use the query you gave above
so when i use with within , it still doesnt work
here you query
SELECT db.*, (ANY v IN [“owner%”,“d%”] SATISFIES LOWER(db.fname) LIKE v END) AS fname_matched, (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;
new verision
SELECT db.* FROM (SELECT RAW META().id FROM db USE INDEX(ix10) WHERE type = “P” AND ANY z WITHIN [LOWER(fname),LOWER(lname),LOWER(name)] SATISFIES (ANY v IN [“owner%”,“d%”] SATISFIES z LIKE v END) END) AS q JOIN db ON KEYS q;
also i tried using LOWER(main) ,LOWER(main.ls.name) instead of LOWER(name)
but they doesnt work