How to sort query results by combining multiple fields(as single field)?

[HariharanKabc]

Hi Everyone, 

We have a use case where we need to sort a result by different fields. E.g. In an existing system we have fields named as countryLang or lang or language in different components, we are introducing some enhancements by giving search and sort option to the system. All these fields have same set of data only, we need to sort the result set by considering all these fields of the components. Is there any way to provide a single alias name in Lucene index and query so that all these fields are considered as same? please let me know if there is any other approach to tackle the problem. It is an existing system with huge content so changing field name is not an option.

E.g

Comp 1 countryLang - en_US

Comp 2 countryLang - fr_FR

Comp 3 lang - en_US

Comp 4 language - de_DE

Expected sort order:

Comp 4 language - de_DE

Comp 1 countryLang - en_US

Comp 3 lang - en_US

Comp 2 countryLang - fr_FR

[Vijayalakshmi_S · Accepted solution]

Hi @HariharanKabc,

We can make use of coalesce function in JCR-SQL2 query. 

This is explained with an example scenario + query in this blog - https://exadel.com/news/aem-tip-merging-on-fields-with-different-names/

Please check and update this thread if you have issues implementing the same for your use case. 

[HariharanKabc]

Hi @Vijayalakshmi_S ,

Thanks for the solution. It is perfectly working for two properties. In our use case we have multiple property names like lang, countryLang, language etc. is there any way to accommodate multiple properties names as well? with some regex or wildcard.

[Vijayalakshmi_S]

Hi @HariharanKabc,

There is no explicit mention about using column/field names with regex in JCR-SQL2 grammar official docs - http://jackrabbit.apache.org/oak/docs/query/grammar-sql2.html

Also, could see that Coalesce function is not accepting more than 2 arguments/field names. 

Given this, I tried something like below (nested coalesce) and it works fine for me. Suggest to try this on similar lines and see if it works for you. 

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'
UNION
SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '/content/demo/language-masters/en/news') AND comp.[sling:resourceType] = 'demoproject/components/content/news'
ORDER BY COALESCE(COALESCE(articleDate,reportDate),newsPublishedDate)