AEM6.3_How to use SQL2 & Xpath Query Tool

Statement - How to use SQL2 & Xpath Query Tool in AEM

Environment - AEM 6.3

Solution -

1. Go to this path http://localhost:4502/crx/explorer/ui/search.jsp?Path=&Query= 
2. User can enter the SQL query or Xpath Query for the search

          Xpath Query : to find a node of givn type
                         1. //element(*, dam:Asset)

  SQL Query : To find a node of givn type

2. select * from [dam:Asset] as cm

Sample Queries :

1. Find all the string contains “Men” under /content

XPath:

/jcr:root/content//element(*)[jcr:contains(@*, 'men’)]

SQL:

SELECT * from [nt:base] AS t

where ISDESCENDANTNODE('/content') and

      contains(t.*, ‘men’)

Query Builder API :

path=/content

fulltext=men

2. Find all nodes under /content created in the past month

XPath:

/jcr:root/content//*

[

((@jcr:created > xs:dateTime('2018-04-22T15:23:57.313Z')

and @jcr:created < xs:dateTime('2018-05-22T15:23:57.313Z')))

]

SQL:

select * from [nt:base] as a

where [jcr:created] > cast('2018-05-22T07:24:50.233Z' as date) and

      [jcr:created] < cast('2018-05-22T07:24:50.233Z' as date) and

      isdescendantnode(a, '/content')

Query Builder:

path=/content

1_relativedaterange.property=jcr:created

1__relativedaterange.lowerBound=-1M

1_relativedaterange.upperBound=0

AEM6.3_How to use Query Debugger tool

Statement - How to use Query Debugger tool

Solution -

1. Go to this path : http://localhost:4502/libs/cq/search/content/querydebug.html
   
   
   
2. This tool is used to perform search queries on JCR
3. For Example - Search all assets under /content/dam folder
4. Use the below query : 

                    type= dam:Asset 

                    path=/content/dam

Similarly we can perform the below Search.

Builder API Queries

How many ACL are in effect under /content/dam

• Use the below query :

                  type= rep:ACL

                  path = /content/dam

• As shown in the screenshot
  

How many web pages are there in the System

• Use the below query

         type=cq:page

         path=/content

How many component under root node or specific path

• Use the below query for specific path

        type=cq:component

        path=/apps/we-retail-screens/components

Or 

• Use the below query for root path

        type=cq:component

        path=/content

Some Different types of cq types as follows and path can be anything based on the need you can choose the path

• sling:OsgiConfig 
• cq:WidgetCollection 
• cq:Widget 
• cq:WorkflowData 
• oak:Unstructured 
• cq:ClientLibraryFolder 
• sling:Mapping 
• cq:Page 
• cq:PageContent 
• cq:WorkflowModel 
• cq:WorkflowLauncher 
• rep:Restrictions 
• nt:folder 
• cq:Payload 
• cq:TabPanel 
• rep:DenyACE 
• rep:GrantACE 
• cq:Panel 
• sling:OrderedFolder 
• rep:ACL 
• nt:unstructured 
• sling:Folder 
• cq:LiveSyncAction 
• cq:Tag 
• nt:file 
• cq:Dialog 
• granite:Task 
• dam:Asset 
• cq:WorkItem 
• cq:WorkflowNode 
• cq:Workflow 
• vlt:PackageDefinition 
• nt:resource 
• dam:AssetContent 
• cq:WorkflowTransition 

How many tags defined

type=cq:Tag
path=/etc/tags

How many total users are defined in the AEM

type=rep:User
path=/home/users

How many (total) indexes have been defined

type=oak:QueryIndexDefinition

How many user groups are defined

type=rep:Group

path=/home/groups 

 List all assets shot with a Canon camera:

path=/content/dam
nodename=metadata
property=tiff:Make
property.value=Canon

How many audit events have accumulated:

type=cq:AuditEvent
path=/var/audit

How many unique users have logged in to AEM in the past 12 hours

A user who has logged in from three different IP addresses (home office, work, mobile phone) will be counted three times.

type=rep:Token
path=/home/users

Search for all DAM assets in /content/dam which are JPG images that were added since March 10, 2018 and to to sort the results in the descending order, 

Execute the following query:

type=dam:Asset
path=/content/dam
property=jcr:content/metadata/dc:format
property.value=image/jpeg
daterange.property=jcr:content/jcr:lastModified
daterange.lowerBound=2018-03-10

orderby.sort=desc 

Find jar files and order them, newest first

http://localhost:4502/bin/querybuilder.json?type=nt:file&nodename=*.jar&orderby=@jcr:content/jcr:lastModified&orderby.sort=desc

1 2 3 4

type=nt:file nodename=*.jar orderby=@jcr:content/jcr:lastModified orderby.sort=desc

Find all pages and order them by last modified

http://localhost:4502/bin/querybuilder.json?type=cq:Page&orderby=@jcr:content/cq:lastModified

1 2	type=cq:Page orderby=@jcr:content/cq:lastModified

Find all pages and order them by last modified, but descending

http://localhost:4502/bin/querybuilder.json?type=cq:Page&orderby=@jcr:content/cq:lastModified&orderby.sort=desc

1 2 3

type=cq:Page orderby=@jcr:content/cq:lastModified orderby.sort=desc

Fulltext search, ordered by score

http://localhost:4502/bin/querybuilder.json?fulltext=Management&orderby=@jcr:score&orderby.sort=desc

1 2 3

fulltext=Management orderby=@jcr:score orderby.sort=desc

Fulltext Search for Women in the path :/content/screen

                 fulltext=women
                 orderby=@jcr:content
                  path=/content/screens

Search for pages tagged with a certain tag

http://localhost:4502/bin/querybuilder.json?type=cq:Page&tagid=marketing:interest/product&tagid.property=jcr:content/cq:tags

1 2 3

type=cq:Page tagid=marketing:interest/product tagid.property=jcr:content/cq:tags

Advantages of Query Builder:

1. Query Builder is easy to read and write
2. No need to hard code time range in it