SQL2 JCR Queries in AEM

Navigating to SQL2 Query editor.

a. Go to CRX DE Lite - http://localhost:4502/crx/de/index.jsp

b. Click on Tools button on the top navigation, then click on Query. This will open a new tab named 'Query' where XPath, SQL or SQL2 queries can be created and executed.

c. Under Type field, select SQL2 option, to execute SQL2 JCR queries.

Below is the way, how conversion happens.

1. Queries are converted into XPath by Query Engine

2. XPath is comverted into JCR SQL2 queries.

3. JCR SQL2 is executed by query engine.

General Query Notation

SELECT * FROM <TYPE OF NODE> WHERE 

ISDESCENDANTNODE(<PATH>) AND

CONDITION1 OR
CONDITION2

1. Get All all nodes of type dam:Asset from /content/dam path

select * from [dam:Asset] as a where isdescendantnode(a,'/ content/dam')

2. Get All nodes of ANY type from /content/dam path

select * from [nt:base] as a where isdescendantnode(a,'/ content/dam')

3. Get All nodes having specific name from given path

select * from [cq:PageContent] as a where 

name()='jcr:content' AND 

isdescendantnode(a, '/ content/wknd')

4. Get All nodes having specific property from given path

select * from [cq:PageContent] as a where 

[jcr:title]='France' AND 

isdescendantnode(a,'/ content')

SELECT * FROM [nt:unstructured] AS node

WHERE ISDESCENDANTNODE(node, "/content/we-retail")

AND [sling:resourceType] = "weretail/components/content/image"

5. Get all nodes of ANY type having ANY property value as 'admin'

select * from [nt:base] where 

contains(*, 'admin')

6. Use built-in JCR functions to match the string and return nodes of type cq:Page.

select * from [cq:Page]

as a where lower([jcr:createdBy])='admin' and

isdescendantnode(a, '/content/wknd')

Example Functions

• fn:upper-case(@data)
• fn:lower-case(test/@data)
• fn:lower-case(fn:name())
• fn:lower-case(fn:local-name())
• fn:string-length(test/@data)
• first([alias])
• upper([data])
• lower([test/data])
• lower(name())
• lower(localname())
• length([test/data])
• length(name())
• name()
• path()

7. Get all nodes who's jcr:title starts with 'fra'

select * from [nt:base]

as a where [jcr:title]

like 'Fra%' and

isdescendantnode(a, '/content')

8. OR operator to return matching nodes having either of the properties.

select * from [nt:base] as a where

([dc:format]='image/jpeg' or [dc:format] = 'image/png') and

isdescendantnode(a, '/content/dam')

9. Get all nodes who's jcr:description is not null

select * from

[dam:AssetContent] as a where

isdescendantnode(a, '/content/dam/we-retail') and

[jcr:description] is not null 

10. Order By clause to oder the results

select * from [dam:AssetContent]

as a where [jcr:description] is null  and

isdescendantnode(a, '/content')

order by [dam:size] ASC

11. contains(param1, param2) function to get the nodes having given property and its value

SELECT * FROM [nt:base] AS nodes 

WHERE CONTAINS(nodes.title, "we-retail") 

SELECT * FROM [nt:unstructured] AS node

WHERE ISDESCENDANTNODE(node, "/content/we-retail")

AND node.[sling:resourceType] = "weretail/components/content/image"

AND CONTAINS(node.[fileReference], "/content/dam/we-retail/en/experiences")

12. Casting an expression from one data type to another one.

SELECT * FROM [cq:PageContent] AS nodes WHERE 

ISDESCENDANTNODE ([/content/we-retail])

AND nodes.[jcr:created] > CAST("2023-04-01T00:00:00.000Z" AS DATE)

SELECT * FROM [cq:PageContent] as nodes WHERE 

ISDESCENDANTNODE ([/content/we-retail])

AND nodes.[navRoot] = CAST("true" AS BOOLEAN)

    Cast Types 

• STRING
• BINARY
• DATE
• LONG
• DOUBLE
• DECIMAL
• BOOLEAN
• NAME
• PATH
• REFERENCE
• WEAKREFERENCE
• URI