AEM - SQL Query with function based indexing for LOWER

[P_V_Nair]

I have a query as below

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/company/creative-assets/opt/campaigns]) AND LOWER(a.[jcr:path]) LIKE '%/100098282_enfr%' ORDER BY a.[jcr:created]

We have created a custom index and created the required index properties including the one for function indexing as below.

This has been re-indexed as well.

Now when we run the above query, query tool gives 0 results with the LOWER function in it 

and when i remove the lower function as below, I get 1 result as below.

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/company/creative-assets/opt/campaigns]) AND a.[jcr:path] LIKE '%/100098282_enfr%' ORDER BY a.[jcr:created]

Result as:

/content/dam/company/creative-assets/opt/campaigns/2022/100098282_enfr.jpg

Since we need to handle case sensitive scenarios as well here like ENFR, EnFr etc. we need lower in the query.

Can someone help me understand why I get 0 results when I add LOWER function to my query and how to fix this issue?

@lukasz-m @Arun_Patidar @Raja-kp @kautuk_sahni @Nupur_Jain 

[P_V_Nair · Accepted solution]

This issue got fixed after using the right function index value in the index. I have updated the screenshot for reference. I had to use fn:lower(@jcr:path) in the index property and that fixed the issue.

[DEBAL_DAS]

Here you could see I have created similar folder hierarchy and uploaded one jpg with similar name -

Query with LOWER function is giving me result -

Could you please test your query using Tools--> Operations--> Diagnosis-->Query Performance --> Explain Query as shown below -

Tested on AEM 6.5.9