[AEM6.5] JCR SQL2 Query to fetch the count of node under a component

[tushaar_srivastava]

Hi Team,

I am trying to write a JCR SQL2 query in AEM 6.5 to get the list of all pages of template /conf/migration/settings/wcm/templates/taxonomy-template which is having more than 1000+ nodes under it.

The scenario i have multiple pages under /content/project.

There are few pages which belongs to /conf/migration/settings/wcm/templates/taxonomy-template

Example: /content/project/page1 And it has multifeild components called as taxonomy which stores the multiple values in different nodes And the structure is like /content/project/us/en/about-us/jcr:content/root/container/taxonomy/codesection.

And inside this we have long list of node.

Now I want to fetch the list of all pages under path /content/project with template type /conf/migration/settings/wcm/templates/taxonomy-template and the nodes will be more than 1000+ under componet 

*/jcr:content/root/container/taxonomy/codesection/

I am trying to do in a way like:

SELECT * FROM [nt:base] AS page
WHERE ISDESCENDANTNODE(page, '/content/project')
AND page.[jcr:content/cq:template] = '/conf/migration/settings/wcm/templates/taxonomy-template'
AND (SELECT COUNT(*) FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE(nodes, page) AND NAME(nodes) = 'codesection') > 1000

 But while running it throws ParseException.

Can anyone please guide how to achive the list of pages have more than 1000+ nodes under a single components in page.

Thanks

@kautuk_sahni  @lukasz-m  @EstebanBustamante 

[lukasz-m · Accepted solution]

Hi @tushaar_srivastava,

Count is not supported by JCR SQL2, so this will not work. I do not think you can achieve your goal using query only. Most likely you will need combine results from query with some java code that will do the rest.

You can have a look on below code snippet.

1. It searches for all pages with /conf/migration/settings/wcm/templates/taxonomy-template template, and that contains child with name codesection
2. It checks if given page has more than 1000 direct child nodes under codesection node.
3. Adds any page that fulfills criteria from step 1 and 2 into result set.

import javax.jcr.query.Query;
import org.apache.commons.collections4.IteratorUtils;

String query = "SELECT * FROM [cq:Page] AS page INNER JOIN [nt:base] AS" 
                + "childnode ON ISDESCENDANTNODE(childnode, page) WHERE ISDESCENDANTNODE(page, '/content/project')"
                + "AND name(childnode) = 'codesection' AND page.[jcr:content/cq:template] = '/conf/migration/settings/wcm/templates/taxonomy-template'";

// set to store results 
Set<Page> pages = new HashSet<Page>();
PageManager pageManager = resourceResolver.adaptTo(PageManager.class);
Iterator<Resource> resourceIterator = resourceResolver.findResources(query, Query.JCR_SQL2);

if (resourceIterator != null) {
    while (resourceIterator.hasNext()) {
        Resource resource = resourceIterator.next();
        if (IteratorUtils.size(resource.listChildren()) > 1000) {
            pages.add(pageManager.getContainingPage(resource.getPath()));
        } 
    }
}

[tushaar_srivastava]

Yes @lukasz-m , Thanks for clarifying, I was trying to get the count through SQL2, thinking we cannot.
let me work on Servlet in order to achieve this.

[Harwinder-singh]

@tushaar_srivastava  JCR SQL2 doesn't support any Aggregation functions like Count , sum, min, max etc.