Index on Functions | Coalesce function in JCR-SQL2

 This post illustrates the index definition for function with sample use case using function named Coalesce in JCR-SQL2 query. 

Recently, we had a query in forum related to Sorting columns/properties with different names - https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/how-to-sort-query-results-by-combining-multiple-fields-as-single/td-p/419215

ie. To sort based on language where the language value is stored against a different property name in each component say, lang/language/compLanguage. Sorting needs to be based on the consolidated values of each of these fields. 

I came across this blog which explains the similar scenario and the solution is to use Coalesce function in JCR-SQL2 query. 

I reproduced the same (as illustrated in the blog above) in my local and it works fine. I haven't tried index definition for Functions before and hence decided to use this example.

Note : I suggest to read through the forum query and the blog before proceeding further for the understanding on the problem statement and the solution using Coalesce function in JCR-SQL2 query. 

Query used :

SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/articles') AND comp.[sling:resourceType] = 'demoproject/components/content/article'

UNION

SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/reports') AND comp.[sling:resourceType] = 'demoproject/components/content/report'

ORDER BY COALESCE(articleDate, reportDate)

Before creating index on function:

Index definition :

Created it in OOTB ntBaseLucene index.

"Explain Query" response:

I will try out index definition for other functions with sample use case in my local and update in upcoming post.