BREAKING NEWS: Exadel named a Leader in this Forrester Agile Software Development Service Providers report... Learn more!  

AEM Tip: Merging on Fields with Different Names

 
 
 

AEM uses Apache Oak to store data in its content repository. Although it’s not a full RDBMS, we can still query it to fetch data using SQL2. It offers many of the features of ANSI SQL has, but it has its limitations. As Apache Oak becomes more mature, more features should be available in SQL2.

As an example of what (we thought at first) was a limitation, we ran into this when we were developing an AEM component for reading and displaying some data from content nodes. There were two types of content nodes to handle, Article and Report, each residing in a different location.

We wanted to merge the items and sort them by a date field to show the newer items first. Unfortunately, the date field to sort on was named differently for Articles and Reports.

Articles had a displayDate field:

Reports had a publishDate field

We needed to write a SQL2 query to sort them by those different fields as if they were the same field.

If this were ANSI SQL, we could make a single alias for both date columns and then sort by that alias:

Although the SQL2 Grammar reference says it supports aliases for columns, the following query didn’t actually perform any sorting:

The result set listed all the Articles first, because they are first in the query. Reports were listed second. With this solution, nothing is really sorted by the specified alias.

Fortunately, the coalesce function came to the rescue. Appearing first in Oak 1.8, it returns the first operand if it is not null, and the second operand otherwise.

This is how our query used that function:

Now, the result set was perfectly sorted by date just as we expected!

Postscript

Indexes on functions are supported in Oak as of 1.6, so we suggest you generate yours using this Index Generator tool and validate it with the Explain Query tool.

NEW! Want to test new technologies like blockchain, AI, mobile, chatbot, and machine learning models? Learn about our Innovation Lab.