The following query is not working on cloud SQL with contains class

[arjunsudhas1995]

I am trying to leverage the following query in AEM as a Cloud service but it is not returning any results.

SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/optu3/en/test/mat-test-directory/id-mvp/business1/solutions/employer/benefit-consultants/find-consultant/sales-rep-details]) and CONTAINS(s.statestag, 'optu3:en/state/CO') and ([sling:resourceType] = 'optu3/components/optu3/sales-rep-detail' OR [sling:resourceType] = 'optu4/components/optu4/content/sales-rep-detail')

when I remove the contains clause this is returning some results. But the same is working perfectly as expected in the AEM 6.3. I tried following community question with solution to use Like clause but the same is not working. The value s.statestag is an String array

[Peter_Puzanovs]

Hi Arjun,

You likely need to update your Lucene index in your AEM as a Cloud service instance,

Please use this guide to create your custom index with the property you need to be available in your search results:

https://experienceleague.adobe.com/docs/experience-manager-cloud-service/content/operations/indexing...

Regards,

Peter

[arjunsudhas1995]

is there any other way to rephrase the same query? 

[arjunsudhas1995]

one more question is could you please explain why CONTAINS(s.statestag, 'optu3:en/state/CO') clause is failing, is that really connected with indexing?

[arunpatidar · Accepted solution]

I tried this as well but didn't work for me then I had to change it to use like operator

SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/optu3/en/test/mat-test-directory/id-mvp/business1/solutions/employer/benefit-consultants/find-consultant/sales-rep-details]) and s.statestag like '%optu3:en/state/CO%' and ([sling:resourceType] = 'optu3/components/optu3/sales-rep-detail' OR [sling:resourceType] = 'optu4/components/optu4/content/sales-rep-detail')