LIKE operator in N1ql query not working


#1
SELECT _id, name,isApprovedExam,security,thumbnail,active,createdAt,modifiedAt,questions,users,revealFeedBack,passPercent,activityInfo,title,subTitle,marks,examCode,modeType,fromDateTime,timeDuration,startActivity,endActivity,instructions 
FROM content  
WHERE _type="activity" AND isApprovedExam=true AND active=true AND userId ="cjkkti4n700rn0a44fyt05fcs" AND LOWER(name) LIKE "%abcdyg%"
ORDER BY createdAt DESC LIMIT 10 OFFSET 0

For the above query there are many records exists ,which satisfies all the condition in the query .Still it is not returning any result.
And for this query it indexing is like

CREATE INDEX `activity` ON `content`(`_id`,`_type`,lower(`name`) DESC,`createdAt` DESC,`organizationId`,`userId`,`active`) WHERE (`_type` = "activity") WITH { "defer_build":true }

if I remove and LOWER(name) like “%abcdyg%" the query is returning multiple records. But I have to filter with name field with LIKE operator.
Am I missing something in the query or index??
How can I use LIKE operator successfully in the above query?


#2

The index will not qualify for query. post the explain. If need design proper index using https://blog.couchbase.com/create-right-index-get-right-performance/

CREATE INDEX activity ON content (userId, active , isApprovedExam, createdAt DESC, lower( name ) DESC ) WHERE ( _type = “activity”) WITH { “defer_build”:true }