AEM SQL 2 Queries Interview Questions
Some of the Most Important Interview Questions Asked in AEM (Adobe Experience Manager) Digital Marketing Tool Interviews
How to find Nodes by type Using AEM SQL2 Query?
SELECT * FROM [nt:file] AS s
The above query finds the nodes of type [nt:file] type. We can further filter the list by comparing node values and property values it is having. In order to compare the nodes from all the types we have to use [nt:base] which is the root node for all the nodes. But, try to narrow the search query to specific nodes to improve the performance of the query.
How to find nodes containing properties with a particular value?
SELECT * FROM [nt:base] AS s WHERE CONTAINS(s.firstName, ‘interviewstuffs’)
The above query finds all the nodes from root node which is having the property value as interviewstuffs. The above functionality can also be accomplished using LIKE constraint. But, Using functions makes the query execution more faster as compared to LIKE constraint.
How to find pages under a path?
SELECT * FROM [cq:page] AS s WHERE ISDESCENDANTNODE(s, ‘/content’)
Above query generates the list of the all the pages which are of type cq:page under the path ‘/content’. We can also get results by changing node type to different types.
How to find Nodes by name?
SELECT * FROM [cq:page] WHERE NAME() = ‘content’
On executing above query we get the result of a page node having the name as ‘content’. If you want to ignore node type prefix and matching names such as jcr:content then use LOCALNAME function.
How to find node with non-empty properties?
SELECT * FROM [cq:pageContent] WHERE [jcr:title] IS NOT NULL
Above Query gets us the results of node having jcr:title property set.
Interview Question to find pages based on jcr:content properties?
FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent)
WHERE ISDESCENDANTNODE(parent, ‘/content’) AND child.[cq:template] = ‘/libs/cq/personalization/templates/hero’
This returns the cq:page nodes along with their child nodes with template name (hero) i.e.., /libs/cq/personalization/templates/hero. It returns the actual page node instead of the node having the template value.
How to find files of particular extension?
SELECT * FROM [nt:file} WHERE NAME() LIKE ‘%.png’
The above query finds all the files with .png extension. It searches the file types in bundles, assets,& DAM, etc..,
How to perform Casting operation on Property values?
SELECT * FROM [nt:base] WHERE boolVal = CAST(‘false’ AS BOOLEAN)
Casting allows us to cast the String values to non-string values such that query operation is smoothly performed.
Casting is permitted on following data types:
Write SQL 2 Query using dates?
FROM [nt:base] AS p
WHERE p.[cq:lastModified] >= CAST(‘2008-01-01T00:00:00.000Z’ AS DATE) AND p.[cq:lastModified] <= CAST(‘2008-12-31T23:49:59.990Z’ AS DATE)
This query returns any node which has been modified in 2008. Date format here is some what tricy as it excepts it in the form of 2008-01-01T00:00:00.000Z which is not possible to easily produce with a SimpleDateFormat. One method for getting the date in the correct format is: