Search is always the backbone of many functionalities in an AEM application . It becomes quite critical in Business scenarios to implement the most Optimized Query which fetches the best possible result. To perform search in AEM , Query Builder is highly recommended over simple SQL / XPATH query statements. The Query Builder , if used correctly, will solve all your query implementations and would be a handy way to Optimize your queries for better performance of the page. Through this Blogpost , I would explain the basics of Query builder and then would go to advanced concepts , focusing at each point how you may create any search scenario to Query Builder Predicate form. I hope this post will solve all your Search performances related hurdles in AEM.
What is Query Builder?
Query Builder is an API which can be used to create Search queries in JAVA content repository. It is extensible tool by which you may add/remove various predicates in a query using this API. The best way to create predicates is using the Query Builder Debugging Tool : /libs/cq/search/content/querydebug.html . Try to implement your Business use case in the Predicate form using this debugger, Optimize the query and then implement it in the code.
Anatomy of a Query:
The query description is a set of predicates which evaluate to an XPATH /JCR query in the backend. To understand more check the screenshot below:
Every Predicate is evaluated using a Predicate Evaluator. There are some in-built predicates in AEM. And you may always customize the predicates and use it as per your Business need. I will go in more details for creating new predicates later.
Implementation :
You may refer to the links : Adobe Doc or Use the API to implement your queries using a Query Builder.
Standard Predicates : Deep understanding of predicates is necessary if you want to Optimize any if your Search Query.
- path : This is used to search under a particular hierarchy only.
- path.self=true : If true searches the subtree including the main node given in path, if false searches the subtree only.
- path.exact=true : If true exact path is matched, if false all descendants are included.
- path.flat=true : If true searches only the direct children .
- type: It is used for searching for a particular nodetype only.
- property: This is used to search for a specific property only.
- property.value : the property value to search . Mutilple values of a particular property could be given using property.N_value=X , where N is number from 1 to N.
- property.depth : The number of additional levels to search under a node. eg. if property.depth=2 then the property is searched under
(@jcr:title = 'foo' or */@jcr:title = 'foo' or */*/@jcr:title = 'foo' )
- property.and : If multiple properties are present , by default an OR operator is applied. If you want an AND , you may use property.and=true
- property.operation : “equals” for exact match (default), “unequals” for unequality comparison, “like” for using the jcr:like xpath function , “not” for no match , (value param will be ignored) or “exists” for existence match .(value can be true – property must exist).
- Example : To Check if a Property Doesnt Exist
path=/content/dam/projects/XX/UA nodename=metadata property=tiff:ImageWidth property.operation=exists property.value=false /jcr:root/content/dam/projects/XX/UA//* [ fn:name() = 'metadata' and not(@tiff:ImageWidth) ]
- fulltext: It is used to search terms for fulltext search
- fulltext.relPath : the relative path to search in (eg. property or subnode) eg. fulltext.relPath=jcr:content or fulltext.relPath=jcr:content/@cq:tags
- daterange : This predicate is used to search a date property range.
- daterange.property : Specify a property which is searched.
- daterange.lowerBound : Fix a lower bound eg. 2010-07-25
- daterange.lowerOperation : “>” (default) or “>=”
- daterange.upperBound: Fix a lower bound eg. 2013-07-26
- daterange.upperOperation: “<” (default) or “<=”
- relativedaterange: It is an extension of daterange which uses relative offsets to server time. It also supports 1s 2m 3h 4d 5w 6M 7y
- relativedaterange.lowerBound : Lower bound offset, default=0
- relativedaterange.upperBound : Upper bound Offset .
- nodename: This is used to search exact nodenames for the result set. It allows few wildcards like: nodename=text* will search for any character or no character after text. nodename=text? will search for any character after text.
- tagid: This predicate is used to search for a particular tag on a page. You may specify the exact tagid of a tag in this predicate
- tagid.property: this may be used to specify the path of node where tags are stored.
- group: This predicate is used to create logical conditions in your query. You can create complex conditions using OR & AND operators in different groups. e.g:
path=/home/users type=rep:User group.1_daterange.property=jcr:created group.1_daterange.lowerBound=2014-08-18 group.1_daterange.upperBound=2014-08-19 group.2_daterange.property=cq:lastModified group.2_daterange.lowerBound=2014-08-18 group.2_daterange.upperBound=2014-08-19 group.p.or=true
- orderBy: This predicate is used to sort the result sets obtained in the query. e.g. orderby=@jcr:score or orderby=@jcr:content/cq:lastModified
- orderby.sort: You may define the sorting way for the search results e.g. desc for descending and “” for ascending.
- orderby:path : this can also be used to sort by path.
- Refining the Results: In order to refine the results there are some parameters which could be leveraged:
- p.hits=full: Use this when you want to return all the properties in a node. Example
- p.hits=selective: Use this if you want to return selective properties in search result. Use this with
p.properties=sling:resourceType jcr:primaryType Example
- p.nodedepth: Use this when you need properties of a node and its child nodes in the same search result. Use this with p.hits=full Example
- p.facets=true : This will be used to Search Facets based search for the assigned Query. If you want to calculate the count of tags which are present in your search result or you want to know how many templates for a particular page are there etc, you may go with Facets based search . Example
type=cq:Page orderby=@jcr:score orderby.sort=desc 1_property=jcr:content/cq:tags 2_property=jcr:content/cq:template 2_property.value=/apps/geometrixx/templates/contentpage p.facets=true
Use this java code to extract Facets for your search result:
Map<String, Facet> facets = result.getFacets(); for (String key : facets.keySet()) { Facet facet = facets.get(key); if (facet.getContainsHit()) { for (Bucket bucket : facet.getBuckets()) { long count = bucket.getCount(); Map<String, String> params = bucket.getPredicate().getParameters(); for (String k : params.keySet()) { out.println(" k:"+k); } } } }
- p.limit : Limits the number of search results fetched.
- p.offset : Sets the offset for the search results
- p.guesstotal : The purpose of p.guessTotal parameter is to return the appropriate number of results that can be shown by combining the minimum viable p.offset and p.limit values.
You may find more such predicates at here.
In most of the cases the standard predicates would solve your purpose of creating Queries for any business scenario. However sometimes we may need to Create Custom Predicates. I will tell you more about this later.
Custom Predicate Evaluators:
Broadly there are 2 kinds of Predicate Evaluators which can be used to create new predicates as per Business need.
- XPath Predicate: This is used to create a Backend XPATH Query using the new custom predicates which can be defined as per need. Many of the inbuilt CQ predicates are XPATH predicates. Notice that in XPATH Predicate Evaluator the overriden method canXpath() should return true while canFilter() should return false. Use the below code snippet to create Custom Predicates :
import org.apache.felix.scr.annotations.Component; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.day.cq.search.Predicate; import com.day.cq.search.eval.AbstractPredicateEvaluator; import com.day.cq.search.eval.EvaluationContext; /** * &amp;amp;amp;lt;code&amp;amp;amp;gt;OriginPredicateEvaluator&amp;amp;amp;lt;/code&amp;amp;amp;gt; queries the Livecopy status of a page. *This property is used to find the Livecopy status of the page. *origin.value=disconnected gives the XPATH query as jcr:content/@jcr:mixinTypes='cq:LiveSyncCancelled' *origin.value=locally gives the XPATH query as jcr:content/@jcr:mixinTypes!='cq:LiveSync' *origin.value=inheritted gives the XPATH query as jcr:content/@jcr:mixinTypes='cq:LiveSync' * @author hakhan */ @Component(metatype = false, factory = "com.day.cq.search.eval.PredicateEvaluator/origin") public class OriginPredicateEvaluator extends AbstractPredicateEvaluator { static final String PE_NAME = "origin"; static final String JCRCONTENT_JCRMIXIN = "jcr:content/@jcr:mixinTypes"; static final String PREDICATE_VALUE = "value"; static final String PREDICATE_LIVESYNCCANC = "'cq:LiveSyncCancelled'"; static final String PREDICATE_LIVESYNC = "'cq:LiveSync'"; static final String OP_EQUALS = "="; static final String OP_NOT_EQUALS = "!="; private static final Logger logger = LoggerFactory .getLogger(OriginPredicateEvaluator.class); @Override public String getXPathExpression(Predicate predicate, EvaluationContext context) { String value = predicate.get(PREDICATE_VALUE); StringBuilder sb = new StringBuilder(); if(value != null){ if (value.equalsIgnoreCase("inheritted")) { sb.append(JCRCONTENT_JCRMIXIN).append(OP_EQUALS); sb.append(PREDICATE_LIVESYNC); } if (value.equalsIgnoreCase("disconnected")) { sb.append(JCRCONTENT_JCRMIXIN).append(OP_EQUALS); sb.append(PREDICATE_LIVESYNCCANC); } if (value.equalsIgnoreCase("locally")) { sb.append(JCRCONTENT_JCRMIXIN).append(OP_NOT_EQUALS); sb.append(PREDICATE_LIVESYNC); } } String xpath = sb.toString(); logger.debug("**********XPATH::**********" + xpath); return xpath; } @Override public boolean canXpath(Predicate predicate, EvaluationContext context) { return true; } @Override public boolean canFilter(Predicate predicate, EvaluationContext context) { return false; } }
- Filter Predicate : This predicate is used whenever you want to Filter out some results which are not needed in the end Search Result. Notice that in Filter Predicate Evaluator the overriden method canXpath() should return false while canFilter() should return true.
import javax.jcr.query.Row; import org.apache.felix.scr.annotations.Component; import org.apache.sling.api.resource.Resource; import org.apache.sling.resource.collection.ResourceCollection; import com.day.cq.search.Predicate; @Component(metatype = false, factory = "com.day.cq.search.eval.PredicateEvaluator/samplepredicate") public class SampleFilterPredicateEvaluator extends AbstractPredicateEvaluator { public static final String SAMPLE = "samplepredicate"; @Override public boolean includes(Predicate p, Row row, EvaluationContext context) { if (!p.hasNonEmptyValue(SAMPLE)) { return true; } /* Write some code logic here as per the condition: Return true for a favourable Condition for keeping the entity in Search Results. Return false for an unfavourable Condition for removing the entity from the Search Results. */ return false; } @Override public boolean canXpath(Predicate predicate, EvaluationContext context) { return false; } @Override public boolean canFilter(Predicate predicate, EvaluationContext context) { return true; } }
Improving Search Performance
By far this is the most important question of any project , and I am telling you its not that difficult. Just a few steps to follow and few things to be aware of and you will be able to optimize the Query to its utmost performance level.
- Tune your AEM for indexing for appropriate nodes. LINK
- Build a Query with the maximum predicates possible for that node , as long as you reduce the Search pool. e.g. If you are searching for a component node with property= sling:resourceType , add nodename predicate too to make the search quicker.
- Keep in consideration what you need in Search Results. If you need cq:Page , it would be bad idea to search for type=nt:unstructured.
- Always check whether the results are upto the Business need, after the grouping and logic you apply in your Predicate based search.
- Try to reduce the processing of the Search results as much as possible. e.g. Its better to use facets then to process the results again
- Go for Custom Predicate Evaluators if you are not able to define your complex query using existing ones or if you think you may simplify the query to a greater level using custom predicates.
- Depending on your application logic, if the result set is more, dont load all the results in DOM and go via partial load using p.limit and p.offset parameters.
- If the search is for anonymous users and no permission sensitive search is needed, use p.guesstotal=true . The purpose of the p.guessTotal parameter is to return the appropiate number of results that can be shown by combining the minimum viable p.offset and p.limit values. Basically it stops the permission check for that session on each node of the result set and makes the Search query performance better.
References
- https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html
- http://www.slideshare.net/alexkli/cq5-querybuilder-adapttoberlin-2011
- http://cq-ops.tumblr.com/post/23543240500/how-to-use-cqs-query-debugger-tool
- http://tech.ethomasjoseph.com/2015/03/tuning-your-jcr-queries-for-aem.html
- http://aemcq5.blogspot.in/2014/11/cq5-querybuilder-simplified.html
I’m not an AEM developer but have been asked to implement search in our AEM website which requires searching multiple paths so I cannot use the basic Search component. Adobe support has told me that I need to use the Query Builder API to do this. I have been unable to find any examples of how to implement it in a web page (form) or component. My question is how do I implement this in a search form on a web page. Any direction you can give is appreciated.
LikeLiked by 1 person
Hi,
Please use the link https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html . It has predicate Query to search for multiple paths.
fulltext=Management
group.p.or=true
group.1_path=/content/geometrixx/en/company/management
group.2_path=/content/geometrixx/en/company/bod
Based on your use case you may utilize the JAVA class shared in the link above with the predicates I shared in this comment.
Regards
Hashim
LikeLike
Can you give some sample code for lucence index with aem quriees wiht latest attributes find
LikeLike
Is there some way to perform a ‘NOT LIKE’ as a property.operation ?
LikeLike
Hi Daniel,
Have you tried the “not” operator in Property predicate ? “not” for no match , (value param will be ignored)
Or if the appropriate predicate is not there you can always go back to SQL Query (eg ):
select * from [nt:base] as t where name(t) = ‘rep:policy’ AND t.[jcr:primaryType] NOT LIKE ‘rep:ACL’
Regards
Hashim
LikeLike
Hi Hashim
Thank you for the very speedy response. The “not” operator is the opposite of the “exists” operator and tests for the absence of a property. I have tried all other combinations but can’t seem to arrive at the equivalent of a NOT LIKE .
With SQL can you point me in the direction of a REST interface, similar to Querybuilder.json that takes a URL query, in SQL format, and returns the results in JSON?
LikeLike
Hi,
Most of the cases are covered by Query Builder , for the few left ones you can always create your custom JAVA class and use the SQL query. Refer to http://www.codermag.net/2016/01/how-to-query-jcr-data-in-adobe-aem-cq.html
Thanks
Hashim
LikeLike
Thank you Hashim Khan, the context is useful for me.
LikeLike
How can we use predective search using Query builder
LikeLike
Have you tried to implement using http://aembloggers.blogspot.com/2015/10/aem-6x-predictive-search-and-spell-check.html
LikeLike
Hello Hashim,
Thanks for your tutorial.It is really very helpful for beginner.
I have one doubt.you have mentioned that “If multiple properties are present , by default an OR operator is applied. If you want an AND , you may use property.and=true”.But i can see that if multiple properties are present AND operator is bydefault applied.Please correct me if i m wrong.
LikeLike
Hi Arpit,
What query are you using ? Please read the link https://docs.adobe.com/docs/en/aem/6-1/develop/search/querybuilder-api.html and check the result. It should be self explanatory.
Thanks
Hashim
LikeLike
Hi Hashim,
Thanks in advance.It is really very helpful.
it would be great if you can clear below question
Is there any limit to have for a group in fulltext search
Im doing an OR of group.
Like eg:
1_group.0_fulltext.relPath= ‘path’
1_group.0_fulltext=’value’
.
.
.
.
1_group.100_fulltext.relPath=’path’
1_group.100_fulltext=’value’
Facing problem if I have more than 50 fulltext OR ed.
Vivek K
LikeLike
Hi,
I dont think so there is a limit to that. But why do you need to do so ? What is the use case for it ? Fulltext search is generally heavy and doing so in multiple paths would make it slow .
Regards
Hashim
LikeLike
Hi Hashim,
Thanks for your article.
The property.depth doesn’t seem working, do you have any example?
Below is my query
path=/etc/tags/geometrixx-media
orderby = @jcr:content/cq:lastModified
property.depth = 1
What I expect is the first level of children, but it returns me all descendants
Thanks
LikeLike
Hi,
You have to specify the property too. eg.:
path=/etc/tags
orderby = @jcr:content/cq:lastModified
property=cq:lastReplicationAction
property.value=Activate
property.depth=3
LikeLike
Hi
How can i get list of all dam assets for an aem page?
LikeLike
Hi,
You can post a Query with the path as current page and for the filereference property. It would get you all the dam assets on that page.
LikeLike
type=sling:OrderedFolder
path=/content
1_property=renderSelect
1_property.value=client
2_property=renderSelectone
2_property.value=server
p.limit=-1
/jcr:root/content//element(*, sling:OrderedFolder)
[
@renderSelect = ‘client’ and @renderSelectone = ‘server’
]
I don’t know where i am wrong. I think by default it should be “OR” operator but it’s coming “and”.
for “and” we have to add property.and=true.
LikeLike
Hi, Please try using group :
type=sling:OrderedFolder
path=/content
group.1_property=renderSelect
group.1_property.value=client
group.2_property=renderSelectone
group.2_property.value=server
group.p.or=true
p.limit=-1
LikeLike
Thanks Hashim for the quick reply. But why “group”? Why it’s not working without “group”.
LikeLike
Hi I need to get assets in /content/dam which does not have a particular property added. how can i write the query.
LikeLike
Hi Could you look into this link http://stackoverflow.com/questions/22510025/how-do-i-add-a-where-not-to-a-querybuilder-query . It would help your scenario.
LikeLike
Hi, Great post.. will enabling querybuilder in Production to make ajax calls impact erformance of Publish environment adversely?
LikeLike
Hi Prahlad,
It depends on the Query which you are making. Generally pages with Search/ Dynamic Content aren’t cached much on Dispatchers, so they are bound to hit Publishers. You can optimize the query as much as possible, provide proper indexing , introduce pagination to reduce the impact.
LikeLike
Hi Monis,
The query looks fine to me. The corresponding Xpath comes out to be :
/jcr:root/content/proj//*
[
(jcr:contains(@projectLocation, ‘bangalore’)
or jcr:contains(@projectName, ‘bangalore’)
or jcr:contains(@projectRating, ‘bangalore’))
and @sling:resourceType = ‘crisil-www/components/content/projectdetails’
]
order by @projectName descending
If this is correct logic for your requirement, check if the resultant nodes have the property “projectName” . If that property is present in the result nodes it should order correctly.
LikeLike
Pingback: Improvise the Search Index | CQ5 AEM Tricks of Trade
Hey great post ! I have a requirement where in I need to display the results(pages) in a hierarchy(tree structure).Here I am using my_map.put( “orderby”, “@” + jcr:content/cq:lastReplicated); ….where I need to apply css to as to show them similar to navigation or menu.Can you help me in this please?
LikeLike
Hi,
You can get the Results based on last: Replicated property. Make sure to use the correct syntax for “@jcr:content/cq:lastReplicated”. After the query is executed you can parse the results in whatever format you need and send to UI.
LikeLiked by 1 person
Thanks for the quick reply.Can you tell me how do I parse them in specific or any reference links you can share ?
I am pretty much new in using the query builder.
LikeLike
Check this link for sample code https://github.com/Adobe-Consulting-Services/acs-aem-samples/blob/master/bundle/src/main/java/com/adobe/acs/samples/search/querybuilder/impl/SampleQueryBuilder.java
LikeLike
Hi Hashim,
Very useful information you shared.
I have particular use case can you please help me how to get these folders use query,
For example I have structure like :
/content/dam/folder1/folder2/multimedia
/content/dam/folder3/folder4/multimedia
Like above I have some folder structure where folders between /dam/ and /multimedia are not fixed. I want to get all the child folders under multimedia. can you please help me.
Thanks,
Kishore.
LikeLike
Hi Kishore, regex is not supported for the path in the query builder. What you can do alternatively is –
1. Add an extra property/mixin to the nodes under multimedia to ease out the search.
2. Create the Query dynamically, using Groups for paths, mentioning all the possible paths present.
LikeLike
Hi Hashim,
I need to search in multiple paths and I am able to do that using SimpleSearch. Now I need to exclude some paths these could be folders or individual pages. How do I do that?
Thanks for your help.
Venkat
LikeLike
Hi Hashim,
I have tried path.self=true but its not including the main node given in path in search.
For example
I have to search fulltext=lightweight for the path /var/commerce/products/egopharm/qvskincare/qv_face/qv_face_oil_free_moi( lightweight is present as a property value for this node)
Query tried=
type=nt:unstructured
fulltext=lightweight
path=/var/commerce/products/egopharm/qvskincare/qv_face/qv_face_oil_free_moi
path.self=true
But its not giving any result. Please help
LikeLike
hi Hashim khan
Can you tell me how i can get all the unused dam assets in aem whose status is unpublished i.e which is not used in any page .. i want to list that type of all assets on a page and on a one click all that dam assets should be deleted from dam…. instead of deleting manually… please help me to figure out some solution..
LikeLike
Great and very informative post. Thanks for putting in the effort to write it. For readers who are interested in Career information. You can use LifePage to explore more than a thousand Career Options. Real IAS officers, real Lawyers, real Businessmen, real CAs, real Actors … explain what is required for success in their profession. These Videos are available for free on the LifePage App: https://www.lifepage.in/app.php
LikeLike
hi Hashim, could you make help me on pagination in AEM query builder search? I ran a query in slingservlet on path ‘/content/website’, adding a custom property on all page’s jcr:content present there, but it took 20 minutes for the output to show on page (used printwriter in servlet). Until then it was blank. So, any thoughts on how to get partial output/pagination in this ?
LikeLike