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

AEM SQL Query

Avatar

Level 6

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.

 

1 Accepted Solution

Avatar

Correct answer by
Employee

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

View solution in original post

5 Replies

Avatar

Community Advisor

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

Avatar

Correct answer by
Employee

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

Avatar

Level 6

Thank you @nitesh_kumar . 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? 

Avatar

Employee

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