Logo Light
  Menu
Lessons
Lessons
  Menu

Understanding SQL2 Queries in AEM

SQL2 is a robust querying language used in Adobe Experience Manager (AEM) for interacting with the Java Content Repository (JCR). It allows for efficient querying of JCR content, offering flexibility and precision. Below are practical examples of SQL2 queries, inspired by common use cases in AEM, demonstrating various functionalities.

Accessing CRX/DE for Query Execution

To execute SQL2 queries in AEM:

  1. Open CRX/DE: First, navigate to your AEM instance and append /crx/de to the URL to open CRX/DE, which is the development environment for AEM​​.
  2. Accessing the Query Tool: In CRX/DE, you will find a “Query” tool under the “Tools” section. Click on this to open the query execution environment.
  3. Type Selection: Select Type as SQL2 from the drop down.

Understanding the Query Language

Selecting All Nodes with a Specific Name

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail")

  • Use Case: Find all nodes named nodeName under the /content/we-retail path.
  • Explanation: This query searches for nodes of type [nt:unstructured] and filters them based on their name and location in the JCR.

Querying All Pages Below a Content Path

SELECT * FROM [cq:Page] AS page
WHERE ISDESCENDANTNODE(page, "/content/we-retail/language-masters/en")

  • Use Case: Retrieve all pages under the English section of the We.Retail site.
  • Explanation: This query focuses on nodes of type [cq:Page], which represent AEM pages, filtering by their path in the JCR.

Finding All Components of a Specific Resource Type

SELECT * FROM [nt:unstructured] AS comp
WHERE ISDESCENDANTNODE(comp, "/content/we-retail/language-masters/en")
AND [sling:resourceType] = "weretail/components/content/heroimage"

Selecting Nodes Where a Property Contains a Specific String

SELECT * FROM [nt:unstructured] AS node
WHERE ISDESCENDANTNODE(node, "/content/we-retail/language-masters/en/men")
AND CONTAINS([fileReference], "/content/dam/we-retail/en/activities/biking/enduro-trail-jump.jpg")

Filtering Nodes Based on Date Properties

SELECT page.* FROM [nt:unstructured] AS page
WHERE ISDESCENDANTNODE(page, "/content/we-retail/language-masters/en/men")
AND page.[cq:lastModified] >= CAST("2014-03-14T21:41:59.339+01:00" AS DATE)

Using Join to Get Specific Components Under a Page.

SELECT comp.* FROM [nt:unstructured] AS page
INNER JOIN [nt:unstructured] AS comp ON ISCHILDNODE(comp, page)
WHERE ISDESCENDANTNODE(page, "/content/we-retail/language-masters/en/men")
AND comp.[sling:resourceType] = 'weretail/components/content/title'

  1. SELECT Clause:
    • SELECT comp.*: This part of the query specifies what is being selected. comp.* means it selects all properties of the nodes aliased as comp.
  2. FROM Clause:
    • FROM [nt:unstructured] AS page: This clause indicates the query is selecting from nodes of type [nt:unstructured] (a common node type in JCR representing unstructured data), and these nodes are being aliased as page. These represent the pages in the AEM site.
  3. INNER JOIN Clause:
    • INNER JOIN [nt:unstructured] AS comp ON ISCHILDNODE(comp, page): This joins the page nodes with other [nt:unstructured] nodes, aliased as comp, where comp nodes are children of page nodes. Essentially, it’s linking pages to their child components.
  4. WHERE Clause – ISDESCENDANTNODE Function:
    • WHERE ISDESCENDANTNODE(page, "/content/we-retail/language-masters/en/men"): This function filters the pages to include only those that are descendants of the specified path. In this case, it’s filtering to pages under the path /content/we-retail/language-masters/en/men in the JCR. This typically represents a specific section or category within the AEM site.
  5. WHERE Clause – Property Filter:
    • AND comp.[sling:resourceType] = 'weretail/components/content/title': This additional filter in the WHERE clause further refines the selection to only those components (child nodes) whose sling:resourceType property equals 'weretail/components/content/title'. This property in AEM typically identifies the specific type or template of a component, and in this case, it’s filtering for title components.

Conclusion

These SQL2 query examples demonstrate the versatility of SQL2 in AEM for querying content within the JCR. They cover various scenarios, from simple name-based searches to more complex queries involving joins and property filters. Understanding these examples can greatly aid in efficiently managing and retrieving content in AEM projects.