Useful Queries in AEM

A collection of various AEM queries that I've found to be useful

Table of Contents

Components

Find all instances of a particular component
Find all instances of a component where a property contains some string
Find all instances of a component where some property is not empty
Find all instances of a component on active pages only
Find all instances of a component that are descendants of a particular parent node name

Pages

Count pages under a particular path
Find pages that use a particular template
Find pages that were activated after a certain date
Find pages that are not active
Find pages that have never been activated and are older than some specific date
Find pages that were created within some date range
Find pages that were last modified by specific user

Strings and Files

Find all instances of a string, excluding a particular path
Find active PDFs in the DAM
Find nodes by name

Components

Find all instances of a particular component (Type: SQL2)

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[sling:resourceType] = 'relative/path/to/component'

---

Find all instances of a component where a property contains some string (Type: SQL2)

SELECT * FROM [nt:base] AS s
WHERE
    ISDESCENDANTNODE([/content]) AND 
    s.[sling:resourceType] = 'relative/path/to/component' AND
    s.[property] LIKE '%string%'

---

Find all instances of a component where some property is not empty (Type: SQL2)

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[sling:resourceType] = 'relative/path/to/component' AND
    s.[property] IS NOT NULL

---

Find all instances of a component on active pages only (Type: SQL2)

SELECT * FROM [cq:PageContent] AS page
INNER JOIN [nt:base] AS component ON ISDESCENDANTNODE(component, page)
WHERE
    ISDESCENDANTNODE(page, [/content]) AND
    page.[cq:lastReplicationAction] = 'Activate' AND
    component.[sling:resourceType] = 'relative/path/to/component'

---

Find all instances of a component that are descendants of a particular parent node name (Type: SQL2)

SELECT * FROM [nt:base] AS parent
INNER JOIN [nt:base] AS component ON ISDESCENDANTNODE(component, parent)
WHERE
    ISDESCENDANTNODE(parent, [/content]) AND
    NAME(parent) = 'node-name' AND
    component.[sling:resourceType] = 'relative/path/to/component'

---

Pages

Count pages under a particular path (Type: SQL2)

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:template] IS NOT NULL

Find pages that use a particular template (Type: SQL2)

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:template] = '/path/to/template'

---

Find pages that were activated after a certain date (Type: SQL2)

SELECT * FROM [nt:base] AS s
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:lastReplicationAction] = 'Activate' AND
    s.[cq:lastReplicated] > '2022-02-25T00:00:00.000-05:00'

---

Find pages that are not active (Type: SQL2)

This query will return pages where lastReplicationAction is either blank or doesn't equal to "Activate".

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:template] IS NOT NULL AND
    (
        s.[cq:lastReplicationAction] <> 'Activate' OR
        s.[cq:lastReplicationAction]  IS NULL
    )

---

Find pages that have never been activated and are older than some specific date (Type: SQL2)

This provides a good way to find unused pages to purge.

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:template] IS NOT NULL AND
    s.[cq:lastReplicationAction] IS NULL AND
    s.[cq:lastModified] < '2020-01-00T00:00:00.000-05:00'

---

Find pages that were created within some date range (Type: SQL2)

SELECT * FROM [nt:base] AS s
WHERE
    ISDESCENDANTNODE([/content]) AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:template] = '/path/to/template' AND
    s.[jcr:created] > '2021-01-01T00:00:00.000-05:00' AND
    s.[jcr:created] < '2022-01-01T00:00:00.000-05:00'

---

Find pages that were last modified by specific user (Type: SQL2)

SELECT * FROM [nt:base] AS s
WHERE
    ISDESCENDANTNODE([/content]) AND 
    s.[cq:template] IS NOT NULL AND
    s.[jcr:primaryType] = 'cq:PageContent' AND
    s.[cq:lastModifiedBy] = 'user'

---

Strings and Files

Find all instances of a string, excluding a particular path (Type: SQL2)

SELECT * FROM [nt:base] AS s 
WHERE
    ISDESCENDANTNODE([/content]) AND
    NOT ISDESCENDANTNODE([/path/to/exclude]) AND
    CONTAINS(*, '"my string"')

---

Find active PDFs in the DAM (Type: XPath)

/jcr:root/content/dam//*[
    @jcr:primaryType = 'dam:AssetContent' and
    @cq:lastReplicationAction = 'Activate' and
    metadata@dc:format = 'application/pdf'
]

---

Find nodes by name (Type: SQL2)

SELECT * FROM [nt:base] AS s
WHERE
    ISDESCENDANTNODE([/content]) AND
    NAME() = 'node-name'

---