Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

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

kautuk_sahni
Employee
Employee

BlogImage.jpg

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.

0 Replies