Friday, 13 November 2015

JCR-SQL2 Queries For AEM / Adobe CQ Developer

JCR-SQL2 queries are useful items in every AEM Developer's toolbag.  They are relatively quick, aren't deprecated and have a familiar SQL like grammer. JCR-SQL2 queries can be invoked from the JCR API:
Session session = ...
QueryManager queryManager = session.getWorkspace.getQueryManager();
Query query = queryManager.createQuery("{QUERY}", Query.JCR_SQL2);
QueryResult result = query.execute();
or through the Sling API:
ResourceResolver resolver = ...
Iterator<Resource> result = resolver.findResources("{QUERY}", Query.JCR_SQL2);
Although there is an official specification for JCR-SQL2, there aren't a ton of really good resources out there nor good references for finding examples of JCR-SQL2 queries.  Below are some queries I've found useful and I think every developer should have as a reference.  Each item lists a concept, provides an sample query and explains the sample.

1. Find nodes by type

SELECT * FROM [nt:file] AS s
This query will find all of the nt:file nodes in the repository.  Users often start by querying against nt:base which is the base type for all JCR nodes, however if you can limit down to a more specific node type, this can have dramatic performance differences.  Just remember that the node containing the properties you're querying against needs to either be the specified type or a decendent of the type.

2. Find nodes with properties containing a value

SELECT * FROM [nt:base] AS s WHERE CONTAINS(s.title, 'client')
This query will find any node with a title property which contains the text client. This can also be accomplished using the LIKE constraint, but is significantly faster as it invokes the Lucene indexes instead of scanning property values.  In a very basic test, I found using CONTAINS to be approximately 100x faster.

3. Finding pages under a path:

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE(s,'/content')
This query will select all nodes of the type cq:Page under the path /content. You could change the node type in the square brackets to query in other node types such as dam:Asset or rep:user.

4. Finding nodes by name:

SELECT * FROM [cq:Page] WHERE NAME() = 'content'
This query will retrieve any cq:Page node with the name "content"; if you used the LOCALNAME function instead of NAME it would ignore the node type prefix, matching names such as jcr:content.

5. Finding non-empty properties

SELECT * FROM [cq:PageContent] WHERE [jcr:title] IS NOT NULL
This query will retrieve cq:PageContent nodes where the jcr:title attribute is set. 
Note: Pages are not required to have a child of type cq:PageContent so if your templates don't set the child node type this may not always return all of the eligible nodes.

6. Finding pages based on jcr:content properties

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/content') AND child.[cq:template] = '/libs/cq/personalization/templates/campaign'
This query returns cq:Page nodes with a child node where the cq:template is set to /libs/cq/personalization/templates/campaign.The really nice thing is that it returns the actual page node instead of the child node which contains the value.

7. Finding files with extension

SELECT * FROM [nt:file] WHERE NAME() LIKE '%.png'
This query will find files with the .png extension. This can be very useful when looking for bundles, assets or other files which have been uploaded.  To search the DAM, change the node type to dam:Asset.

8. Cast Property Values

SELECT * FROM [nt:base] WHERE hideInNav = CAST('true' AS BOOLEAN)
Casting allows you to query accurately based on non-String properties.  You can cast into any of the following types:
  • STRING
  • BINARY
  • DATE
  • LONG
  • DOUBLE
  • DECIMAL
  • BOOLEAN
  • NAME
  • PATH
  • REFERENCE
  • WEAKREFERENCE
  • URI

9. Querying on dates

SELECT p.* FROM [nt:base] AS p WHERE p.[cq:lastModified] >= CAST('2008-01-01T00:00:00.000Z' AS DATE) AND p.[cq:lastModified] <= CAST('2008-12-31T23:59:59.999Z' AS DATE)
This query will find any node which has been modified in 2008. Date conversion for JCR-SQL2 can be somewhat interesting as it expects the date parameter to be in the format 2008-01-01T00:00:00.000Z which is not possible to easily produce with a SimpleDateFormat. One method for getting the date in the correct format is:
Calendar cal;
ValueFactoryImpl.getInstance().createValue(cal).getString();

Wrapup

Hopefully, you find these query examples useful, if you see any mistakes or anything I missed, please leave a comment!
References:

7 comments :

  1. Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important. Project Center in Chennai It’s what organizations do with the data that matters. Big data can be analyzed for insights that lead to better decisions and strategic business moves.

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.
      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai


      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Hi Dude,
    Awesome Post!!! With unique content, I really get interest to read this post. I hope this article help many of them who looking this pretty information.
    Regards,
    cloud computing training chennai|cloud computing training centers in chennai

    ReplyDelete
  3. Excellent post!!! In this competitive market, customer relationship management plays a significant role in determining a business success. That too, cloud based CRM product offer more flexibility to business owners to main strong relationship with the consumers. Cloud Computing Training in Chennai|cloud computing training centers in chennai

    ReplyDelete
  4. The expansion of internet and intelligence in business process lead the way to huge volume of data. It is important to maintain and process these data to be efficient in data handling. Hadoop Training in Chennai | Big Data Training in Chennai

    ReplyDelete
  5. Great Article
    Cloud Computing Projects


    Networking Projects

    Final Year Projects for CSE


    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete