Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

convert xpath query into SQL2 query

Avatar

Level 4

i need to convert below xpath query 

 

type=cq:PageContent
path=/content/en
property=xxx/sling:resourceType
property.value=xx/header
dateComparison.property1=cq:lastModified
dateComparison.property2=cq:lastReplicated
dateComparison.operation=greater

to 

AEM SQL2 query

i tried - select * from [cq:PageContent] as a where [xxx/sling:resourceType]='xx/header' and isdescendantnode(a,'/content/en') and
a.[cq:lastModified] > a.[cq:lastReplicated]

 

but i am getting error as expected static operand

 

please let me know the correct SLQ2 query for above

 

 

1 Accepted Solution

Avatar

Correct answer by
Level 6

Hello @sonuk85184451 

 

One easy way to get SQL equivalent of a Querybuilder/Xpath is to use AEM logs.

 

Steps:

1. Refer to [0] to configure a new logger in debug mode.

2. Now, when you execute a query Querybuilder/Xpath query, its equivalent would be logged like 

13.12.2022 09:51:13.715 *DEBUG* [[0:0:0:0:0:0:0:1] [1670905273708] GET /libs/cq/search/content/querydebug.html HTTP/1.1] org.apache.jackrabbit.oak.query.QueryImpl query execute select [jcr:path], [jcr:score], * from [cq:PageContent] as a where [xxx/sling:resourceType] = 'xx/header' and isdescendantnode(a, '/content/en') /* xpath: /jcr:root/content/en//element(*, cq:PageContent)[(xxx/@sling:resourceType = 'xx/header')] */

The query statement has both sql and xpath equivalents.

 

In your case, the query part is only

type=cq:PageContent
path=/content/en
property=xxx/sling:resourceType
property.value=xx/header

While following filters are applied on top of query:

dateComparison.property1=cq:lastModified
dateComparison.property2=cq:lastReplicated
dateComparison.operation=greater

 

If you wish to use SQL, you might to do similar. Query and then filter.

 

[0]https://aemtreasury.wordpress.com/2015/11/13/how-to-enable-logging-of-aem-query/

View solution in original post

4 Replies

Avatar

Community Advisor

Hi @sonuk85184451,

I have tried using AEM Query Builder debugger URL along with your query as below

http://localhost:4502/libs/cq/search/content/querydebug.html?_charset_=UTF-8&query=type%3Dcq%3APageC...

It gives you XPath query as below

Screen Shot 2022-12-12 at 2.57.44 PM.png

 

You may now refer to Querying and Searching using JCR[0] documentation to convert them.

Below are some of the mappings between xpath and sql2 queries respectively

    //*                                         SELECT * FROM [nt:base]
   //element(*,my:type)                         SELECT * FROM [my:type]
  //element(*,my:type)/@my:title                SELECT [my:title] FROM [my:type]
 //element(*,my:type)/(@my:title | @my:text)    SELECT [my:title],[my:text] FROM [my:type]
//element(*,my:type)/(@my:title union @my:text) SELECT [my:title],[my:text] FROM [my:type]

[0]: https://docs.jboss.org/jbossdna/0.7/manuals/reference/html/jcr-query-and-search.html

Hope that helps!

Regards,

Santosh

Avatar

Level 9

Hi @sonuk85184451 

 

Issue with your SQL2 query is with comparing the dates a.[cq:lastModified] > a.[cq:lastReplicated]. I have executed rest of the query in my local and it's working fine. You would need to use CAST operator in your query. For example, this query would work:-

 

select * from [cq:PageContent] as a where [sling:resourceType]='xx/components/structure/csh-page' and isdescendantnode(a,'/content/xx/en') and
a.[cq:lastModified] > CAST("2019-01-01T00:00:00.000Z" AS DATE) 

 

Also, similar issue has been discussed in the following post:-

 

https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/compare-date-field-in-jcr-... 

 

Another alternative would be to use XPath query.

 

Hope this helps.

Avatar

Level 4

Thanks below query will be working as a.[cq:lastModified] > CAST("2019-01-01T00:00:00.000Z" AS DATE)  but i am looking to compare a.[cq:lastModified] > a.[cq:lastReplicated]

 

let me know incase this condition can be translated into SQL query by CAST operator 

 

select * from [cq:PageContent] as a where [sling:resourceType]='xx/components/structure/csh-page' and isdescendantnode(a,'/content/xx/en') and
a.[cq:lastModified] > CAST("2019-01-01T00:00:00.000Z" AS DATE) 

Avatar

Correct answer by
Level 6

Hello @sonuk85184451 

 

One easy way to get SQL equivalent of a Querybuilder/Xpath is to use AEM logs.

 

Steps:

1. Refer to [0] to configure a new logger in debug mode.

2. Now, when you execute a query Querybuilder/Xpath query, its equivalent would be logged like 

13.12.2022 09:51:13.715 *DEBUG* [[0:0:0:0:0:0:0:1] [1670905273708] GET /libs/cq/search/content/querydebug.html HTTP/1.1] org.apache.jackrabbit.oak.query.QueryImpl query execute select [jcr:path], [jcr:score], * from [cq:PageContent] as a where [xxx/sling:resourceType] = 'xx/header' and isdescendantnode(a, '/content/en') /* xpath: /jcr:root/content/en//element(*, cq:PageContent)[(xxx/@sling:resourceType = 'xx/header')] */

The query statement has both sql and xpath equivalents.

 

In your case, the query part is only

type=cq:PageContent
path=/content/en
property=xxx/sling:resourceType
property.value=xx/header

While following filters are applied on top of query:

dateComparison.property1=cq:lastModified
dateComparison.property2=cq:lastReplicated
dateComparison.operation=greater

 

If you wish to use SQL, you might to do similar. Query and then filter.

 

[0]https://aemtreasury.wordpress.com/2015/11/13/how-to-enable-logging-of-aem-query/