Index on Functions | Coalesce function in JCR-SQL2 | AEM Community Blog Seeding

[kautuk_sahni]

Index on Functions | Coalesce function in JCR-SQL2 by Vijayalakshmi | myaemlearnings

Abstract

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)

Read Full Blog

Index on Functions | Coalesce function in JCR-SQL2

Q&A

Please use this thread to ask the related questions.