AEM SQL Query | Community
Skip to main content
P
Adobe Champion
P_V_NairAdobe Champion
Adobe Champion
January 18, 2023
Solved

AEM SQL Query

  • January 18, 2023
  • 2 replies
  • 2288 views

I have a requirement where I run a daily scheduler which picks all changed nodes to a dam location , say /content/test with changes made between today(when the scheduler is running) and  the last run(yesterday's date).

Can someone help out on how the SQL query will be?

 

I have a basic query which checks all nodes with the last modified date

 

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/test]) AND a.[jcr:content/jcr:lastModified] > CAST('2022-06-23T21:30:00.000Z' AS DATE)

 

How to check between two dates and fetch those nodes which was changed between today and yesterday?

Any leads appreciated.

 

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 nitesh_kumar-1

Hi @p_v_nair ,

 

For SQL2-based query, you need to provide a date range, something like this:- 

 

SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/wknd/en/site]) AND a.[jcr:content/jcr:lastModified] > CAST('2022-06-01T21:30:00.000Z' AS DATE) AND a.[jcr:content/jcr:lastModified] < CAST('2023-01-12T21:30:00.000Z' AS DATE)

 

Hope this helps!

 

Regards,

Nitesh

2 replies

ChitraMadan
Community Advisor
ChitraMadanCommunity Advisor
Community Advisor
January 18, 2023

Hi @p_v_nair ,

Please find the query below:

 

Map<String, String> map = new HashMap<>(); map.put("group.p.or", "true"); map.put("group.1_daterange.property", "jcr:content/jcr:lastModified"); // update lowerBound date by last run date map.put("group.1_daterange.lowerBound", "2022-06-23"); map.put("group.2_daterange.property", "jcr:content/jcr:created"); map.put("group.2_daterange.lowerBound", "2022-06-23"); map.put("path", "/content/dam/test"); map.put("type", "dam:Asset"); map.put("p.limit", "-1"); PredicateGroup predicateGroup = PredicateGroup.create(map); Query query = queryBuilder.createQuery(predicateGroup, session); SearchResult result = query.getResult();

 

 

Above query will give you any asset created or modified after 23rd June, in the specified location.

If you want to run the query between two dates, add upperBound too like below:

 

 

Map<String, String> map = new HashMap<>(); map.put("group.p.or", "true"); map.put("group.1_daterange.property", "jcr:content/jcr:lastModified"); // update lowerBound date by last run date map.put("group.1_daterange.lowerBound", "2022-06-23"); map.put("group.1_daterange.upperBound", "2022-06-25"); map.put("group.2_daterange.property", "jcr:content/jcr:created"); map.put("group.2_daterange.lowerBound", "2022-06-23"); map.put("group.1_daterange.upperBound", "2022-06-25"); map.put("path", "/content/dam/test"); map.put("type", "dam:Asset"); map.put("p.limit", "-1"); PredicateGroup predicateGroup = PredicateGroup.create(map); Query query = queryBuilder.createQuery(predicateGroup, session); SearchResult result = query.getResult();

 

 

This query will give you any asset created or modified between 23rd Jun and 25th Jun.

 

Thanks,

Chitra

    nitesh_kumar-1
    Adobe Employee
    nitesh_kumar-1Adobe EmployeeAccepted solution
    Adobe Employee
    January 18, 2023

    Hi @p_v_nair ,

     

    For SQL2-based query, you need to provide a date range, something like this:- 

     

    SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/wknd/en/site]) AND a.[jcr:content/jcr:lastModified] > CAST('2022-06-01T21:30:00.000Z' AS DATE) AND a.[jcr:content/jcr:lastModified] < CAST('2023-01-12T21:30:00.000Z' AS DATE)

     

    Hope this helps!

     

    Regards,

    Nitesh

      P
      Adobe Champion
      P_V_NairAdobe ChampionAuthor
      Adobe Champion
      January 19, 2023

      Thank you @nitesh_kumar-1 . That worked perfectly fine. If I need to add one more different path along with 

      /content/dam/wknd/en/site

      to this query, do I need to add to add another or and add the path? 

        nitesh_kumar-1
        Adobe Employee
        nitesh_kumar-1Adobe Employee
        Adobe Employee
        January 19, 2023

        Yes, You could combine it using "OR", something like this:-

        SELECT * FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE([/content/dam/wknd/en/site]) or ISDESCENDANTNODE([/content/dam/wknd/en/site2]) AND a.[jcr:content/jcr:lastModified] > CAST('2022-06-01T21:30:00.000Z' AS DATE) AND a.[jcr:content/jcr:lastModified] < CAST('2023-01-24T21:30:00.000Z' AS DATE)

         

        Regards,

        Nitesh

          Terms & ConditionsAccessibility statement

          Loading footer...