convert xpath query into SQL2 query | Community
Skip to main content
S
sonuk85184451
Level 4
December 12, 2022
Solved

convert xpath query into SQL2 query

  • December 12, 2022
  • 3 replies
  • 5154 views

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

 

 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by aanchal-sikka

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/

3 replies

SantoshSai
Community Advisor
SantoshSaiCommunity Advisor
Community Advisor
December 12, 2022

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%3APageContent%0D%0Apath%3D%2Fcontent%2Fen%0D%0Aproperty%3Dxxx%2Fsling%3AresourceType%0D%0Aproperty.value%3Dxx%2Fheader%0D%0AdateComparison.property1%3Dcq%3AlastModified%0D%0AdateComparison.property2%3Dcq%3AlastReplicated%0D%0AdateComparison.operation%3Dgreater

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

Santosh Sai
    Avinash_Gupta_
    Community Advisor and Adobe Champion
    Avinash_Gupta_Community Advisor and Adobe Champion
    Community Advisor and Adobe Champion
    December 12, 2022

    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-query/m-p/217579 

     

    Another alternative would be to use XPath query.

     

    Hope this helps.

      S
      sonuk85184451Author
      Level 4
      December 14, 2022

      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
      Community Advisor
      aanchal-sikkaCommunity AdvisorAccepted solution
      Community Advisor
      December 13, 2022

      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/

      Aanchal Sikka
        Terms & ConditionsAccessibility statement

        Loading footer...