convert xpath query into SQL2 query

[sonuk85184451]

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

[SantoshSai]

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

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

[Avinash_Gupta_]

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.

[sonuk85184451]

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) 

[aanchal-sikka · Accepted solution]

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/