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.

Comments

Post a Comment

Popular posts from this blog

Embedding Third party dependency/OSGi bundle in AEM application hosted in AEMasCS

Image
This post illustrate the ways of embedding third party dependency in AEM maven project. Steps are mentioned considering the project structure to be followed for AEMasCS while the same can be followed for classic versions too.  Before we begin, I would like to explain the scenario of including a dependency in our AEM maven project.  When we would like to make use of an API that is not already available in our project, we look for that in maven-central repository and specify the dependency in main pom.xml and desired module's pom.xml file. (Lets say, AWS S3 Java SDK V2 dependency) Upon updating the project in the IDE, we would see our class files (making use of those APIs) are compiled/build successfully without any errors.  Now, in the context of AEM/OSGi environment, when the same package and hence the bundle is deployed to AEM instance, dependencies that we added new might not be resolved/available in the OSGi as a bundle. Something like the below This indicates that the added dep
Read more

OSGI Factory Configuration implementation

OSGI Factory configuration is used to have many instances of an OSGI config (represented/identified via an identifier) for the single OSGI service/PID. Most often used OOB Factory configurations for example: Logger Factory/writer configuration (for log statements) Service user Mapper service Amendment (for using service resource resolver in projects) Few sample real time use case for the need of OSGI factory config: For multi tenant projects, if we have generic service used across tenants with properties/attributes specific to tenant. For single tenant, generic service targeting all locales with properties/attributes specific to locale. (where generic service -> holds logic in the lines of calling third party service for retrieving/updating information.) Implementation: ( In terms of OSGI R6 annotations ): Create Object Class Definition(OCD) defining desired properties for the OSGI service. - OSGIFactoryConfigOCD.java OSGI Service/Factory service to retrieve
Read more

Creation of Template Types for Editable templates

Image
In this post, we will create templates types which is the base for creating editable templates. Before getting to the subject directly, have added an introduction about templates in general. Template is the base for creating pages. Starting AEM 6.2, we have categorisation for templates - Static and Editable templates. Static templates :  This has been available for several versions of AEM/CQ Created using "Create Template" option with base page component as resourceType (which is responsible for rendering the page structure).  Template ( base page component as resourceType ) -> Page This cannot be edited as such. Since it just holds a reference to base page component, any changes to the page (other than authorable components), would need a change in base page component which is ultimately a work of developer. (calls for a code change) Allowed components are configured using Design mode (list being saved to etc/designs/projectfolder/templatename/jcr:content)
Read more