Java content repository- Structured Query Language 2 better known as JCR-SQL2 is a new specification that helps us to query data stored as properties under Nodes. It is a valuable tool for AEM developers to have knowledge of this.
This article will explain various JCR queries in detail.
A basic understanding of SQL is a plus but now a mandate. Read on folks...
The 'SELECT' statement.
The first newbie command that we all try our hands-on first. :)
i. Select Everything
SELECT * FROM [nt:base]
Selects every node from the content repository. This is because nt:base is the base type for all JCR nodes.
ii. Select Nodes of a given type.
SELECT * FROM [my:type]
SELECT * FROM [nt:file]
SELECT * FROM [cq:Page]
The 1st query collects all nodes of jcr:PrimaryType = my:Type
The 2nd collects nodes where jcr:PrimaryType = nt:File, this is generally required to collect all nodes of nt:File type.
The 3rd collects all nodes of jcr:PrimaryType = cq:Page, is commonly used in Adobe Experience Manager to collect Pages.
iii. Find Nodes by Name
SELECT * FROM [nt:base] As nodes WHERE NAME(nodes) = 'jcr:content'
This will select all nodes of type nt:base having the name "jcr:content".
iv. Finding Nodes Under a path.
SELECT * FROM [nt:unstructured] As node WHERE ISDESCENDANTNODE ([/content/myProject])
This will return all nodes of type nt:unstructured under the given path /content/myProject
v. Finding Nodes Under MULTIPLE paths.
SELECT * FROM [nt:unstructured] AS s WHERE (ISDESCENDANTNODE('/content/myProject/Path1')
or
ISDESCENDANTNODE('/content/myProject/Path2'))
This will return all nodes of type nt:unstructured under 2 given paths /content/myProject/Path1 and /content/myProject/Path2
vii. Find Nodes based on property value.
SELECT * FROM [nt:base] AS nodes WHERE CONTAINS(nodes.title, 'news')
This will find all the nodes under the repository whose title contains the word "news". This can also be done using the LIKE clause but is not very efficient and hence slow.
SELECT * FROM [nt:base] AS nodes WHERE nodes.title LIKE 'news'
viii. Find Nodes based on MULTIPLE property values.
SELECT * FROM [nt:unstructured] WHERE title = 'fruit' AND color = 'red'
This will find all the nt:unstructured nodes under the repository whose title contains the word "fruit" and has the color property as "red". You can also use the CONTAINS and LIKE clause here if you want to, just like the previous example.
The JCR 'LIKE' Clause
The LIKE clause is generally used for matching the attribute values.
i. Finding Nodes with an exact match in the title.
SELECT * from [cq:Page] where title LIKE 'News'
This will return all the nodes of type cq:Page where there is a title attribute EXACTLY Matching the word "News".
ii. Finding Nodes whose title contains a word/phrase.
SELECT * from [cq:Page] where title LIKE '%News%'
This will return all the nodes of type cq:Page where there is a title attribute WHICH CONTAINS the word "News".
iii. Finding files that match the given file extension.
SELECT * FROM [nt:file] WHERE NAME() LIKE '%.txt'
This is used to find all the nodes of type nt:file whose name has a (.txt) file extension (text files).
SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:file]
WHERE LOCALNAME() LIKE '%.png'
Similarly, the above query selects some attributes of the nt:file node for all files having the (.png) extension.
iv. Find Nodes under a given path.
SELECT * FROM [my:type] WHERE PATH([my:type]) LIKE '/content/myProject'
This will return all nodes of type "my:Type" under the path /content/myProject/.
v. Find Nodes under a given path and its subtree.
SELECT * FROM [my:type] WHERE PATH([my:type]) LIKE '/content/myProject/%'
This will return all nodes of type "my:Type" under the path /content/myProject/ and all its subtrees /content/myProject/*
The Jcr 'IS NOT NULL' Property
The 'IS NOT NULL' property is used to check for NON NULL values.
SELECT * FROM [my:type]WHERE [my:type].prop1 IS NOT NULL
SELECT * from [cq:Page] where image IS NOT NULL
In both the above queries, the result is a set of the node where the given properties are not null.
So if the values of the property are NULL in any case then the results will not be returned by the query. This means that the query will overlook any node having no value for the given property.
The JCR 'Order By' Clause
SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE ([/content/myProject])
ORDER BY [jcr:lastModified]
This will return all the nodes of type nt:unstructured in the given path /content/myProject ordered by the last modified time in increasing order.
Decreasing Order:
SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE ([/content/myProject])
ORDER BY [jcr:lastModified] DESC
JCR Query using Dates
SELECT * FROM cq:PageContent AS s
WHERE s.[jcr:created] >= CAST('2015-01-01T00:00:00.000Z' AS DATE)
AND s.[jcr:created] < CAST('2016-01-01T00:00:00.000Z' AS DATE)
This query will find all the nodes of type cq:PageContent which has been created between the given two dates.
This shows you how to query using dates in JCR SQL2.
JCR SQL2 'CAST' Property Values
BOOLEAN:
SELECT * FROM [my:type] WHERE prop1 = CAST('true' AS BOOLEAN)
SELECT * FROM [nt:unstructured] WHERE hideInNav = CAST('true' AS BOOLEAN)
LONG:
SELECT * FROM [my:type] WHERE PATH([my:type])> LIKE '/content/%' AND DEPTH([my:type]) = CAST(2 AS LONG)
DATE:
SELECT * FROM cq:PageContent AS s
WHERE s.[jcr:created] >= CAST('2015-01-01T00:00:00.000Z' AS DATE)
AND s.[jcr:created] < CAST('2016-01-01T00:00:00.000Z' AS DATE)
The examples above show some JCR SQL2 queries using the CAST property.
The CAST property can be used for the following data types.
BINARY DOUBLE REFERENCE
BOOLEAN LONG STRING
DATE NAME URI
DECIMAL PATH WEAKREFERENCE
No comments:
Post a Comment
If you have any doubts or questions, please let us know.