AEM Query on Closing Date Date Range

[touseefk2181136]

I have following AEM query to get such shops which has closing date greater than current date and it works fine. 

path=/content/data/shops
type=nt:unstructured
group.p.or=true
group.1_daterange.property=closingDate
group.1_daterange.lowerBound=2024-10-27T00:00:00

But there is possibility that closingDate property might not exist in the backend JCR or closingDate might be empty. I need to get such shops as well. I am using following query but it doesn't work. Kindly help to apply these conditions too.

path=/content/data/shops
type=nt:unstructured
group.p.or=true
group.1_daterange.property=closingDate
group.1_daterange.lowerBound=2024-09-27T00:00:00

group.2_property=closingDate
group.2_property.value=

group.3_property=closingDate
group.3_property.operation=exists
group.3_property.value=false

Thanks in advance.

[arunpatidar]

Hi @touseefk2181136 

path=/content/data/shops
type=nt:unstructured
group.1_property=closingDate
group.1_property.value=false
group.1_property.operation=exists

group.2_daterange.property=closingDate
group.2_daterange.lowerBound=2024-10-27T00:00:00

group.p.or=true

For empty condition, please check https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/aem-query-for-property-wit... 

[touseefk2181136]

@arunpatidar thanks for sharing. I have three scenarios, it works for two scenarios but not the third one

1. Apply date filter correctly (works fine)
2. Get those shops as well which doesn't have closingDate property (works fine)
3. Get those shops as well where closingDate property exists but it is empty (Doesn't work)

[arunpatidar · Accepted solution]

Hi @touseefk2181136,

First you need to write a query to get all the nodes which has closingDate property.

then iterate through all the results and filter out only with empty date and closing date greater than current date.

another way to create custom predicate https://github.com/arunpatidar02/aemaacs-aemlab/blob/master/core/src/main/java/com/community/aemlab/... 

[touseefk2181136]

@arunpatidar thanks, custom predicate helped to achieve this.

[h_kataria]

It might work with xpath query as below : 

(
  /jcr:root/content/path//*
  [
  ((@test > xs:dateTime('2024-09-27T00:00:00.000Z')))
  ]
|
  /jcr:root/content/path//*
  [
  (not(@test))
  ]
|
  /jcr:root/content/path//*
  [
  (@test = '')
  ]
)