JCR-SQL2 Query with Examples

Introduction

OOTB Apache OAK allow to create queries on SQL(deprecated), XPath(deprecated), JCR-JQOM and JCR-SQL2. It’s obvious that there is not a good point to explain deprecated SQL and XPath. This is a little How-To article which try to cower JCR-SQL2 queries for Apache Jackrabbit/OAK. Apache OAK has a more strict JCR-SQL2 syntax therefore some queries which are working correct on Apache Jackrabbit wouldn’t be executed on Apache OAK so we should always hold this in mind. Anyway let’s deal with JCR-SQL2 queries.
There is two ways how JCR-SQL2 query can be executed from Java code:

• JCR API:

  Session session = ... ;
  Workspace workspace = session.getWorkspace();
  QueryManager queryManager = workspace.getQueryManager();
  Query query = queryManager.createQuery("{JCR-SQL2 query}", Query.JCR_SQL2);
  QueryResult result = query.execute();

• Sling API

  ResourceResolver resolver = ...;
  Iterator<Resource> result = resolver.findResources("{JCR-SQL2 query}", Query.JCR_SQL2);

Note: If you obtaining some data from the user side for JCR-SQL2 query it must be escaped with

import org.apache.jackrabbit.oak.query.SQL2Parser;
...
SQL2Parser.escapeStringLiter‌al("String for escape");
...

SQL2Parcer can be added to maven as below dependency:

<dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>oak-core</artifactId>
</dependency>

JCR-SQL2 Queries examples

Find pages

SELECT p.* FROM [cq:Page] AS p

Cast property value

By default we are using strings within queries constraints, but as JCR properties can hold not only values of String type we can cast string to required property type. We can cast to this property types: STRING, BINARY, DATE, LONG, DOUBLE, DECIMAL, BOOLEAN, NAME, PATH, REFERENCE, WEAKREFERENCE, URI.

SELECT p.* FROM [cq:Page] AS p
WHERE p.[jcr:created] > CAST('2015-09-22T12:33:01.042Z' AS DATE)

Find pages constrained by path

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])

Find pages with constraint by path and name

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
AND
NAME(p)='geometrixx_user_grou'

Find pages with inner nodes properties constrain

SELECT p.* FROM [cq:Page] AS p
WHERE ISDESCENDANTNODE(p, [/content/geometrixx/en])
AND p.[jcr:content/author] = 'Henry Ford'

Search with LIKE

LIKE – is a predicate for regular expression search. Basically it works like “=” but supports additional regular expressions:

SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE 'Henry Ford%'

SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE '%Henry Ford%'

SELECT p.* FROM [cq:PageContent] AS p
WHERE p.[author] LIKE 'H_n_y Ford'

Full-text search with CONTAINS

CONTAINS – is a predicate for full-text search which allow to specify additional expression within search expression:

SELECT p.* FROM [cq:PageContent] AS p
WHERE CONTAINS(p.[author], '{Henry Ford}')

SELECT p.* FROM [cq:PageContent] AS p 
WHERE CONTAINS(p.[author], '{Hen*}')

SELECT p.* FROM [cq:PageContent] AS p 
WHERE CONTAINS(p.[author], '{Henry} OR {Josephine} OR {John Doe}')

If you are using full-text queries you should keep in mind that if content wasn’t indexed or indexes are corrupted – full-text queries returns nothing or some nodes will be skipped from a result.

JOIN the results

Apache OAK supports INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN.

Just to refresh in memory:

• JOIN clause is used to combine rows from two or more tables, based on a common field between them
• INNER JOIN – returns all rows when there is at least one match in BOTH tables
• LEFT OUTER JOIN – return all rows from the left table, and the matched rows from the right table
• RIGHT OUTER JOIN – return all rows from the right table, and the matched rows from the left table

SELECT parent.* FROM [cq:Page] AS parent 
INNER JOIN [cq:PageContent] AS child 
ON ISCHILDNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent, [/content/geometrixx-outdoors/en]) 
AND child.[cq:template] = '/libs/social/blog/templates/page'

SELECT meta.* FROM [nt:base] AS meta 
INNER JOIN [cq:Taggable] AS taggable
ON ISSAMENODE(meta,taggable)
WHERE ISDESCENDANTNODE(meta, [/content/dam/geometrixx/banners])

SELECT parent.* FROM [cq:Page] AS parent 
RIGHT OUTER JOIN [cq:PageContent] AS child 
ON ISCHILDNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent, [/content])

UNION the results

The UNION operator is used to combine the result-set of two or more SELECT statements. Therefore below notice is mandatory.

Notice:

1. each SELECT statement within the UNION must have the same number of columns.Data types of the columns should be compatible
2. This easy rules allow to use UNION operator with fun.

SELECT parent.* FROM [cq:Page] AS parent 
RIGHT OUTER JOIN [cq:PageContent] AS child 
ON ISCHILDNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent, [/content/site1/en/blog])
UNION
SELECT parent.* FROM [cq:Page] AS parent 
RIGHT OUTER JOIN [cq:PageContent] AS child 
ON ISCHILDNODE(child,parent) 
WHERE ISDESCENDANTNODE(parent, [/content/site2/en/blog])