Query to fetch assets with either offTime doesn't exists or offTime greater than current time.

[jezwn]

Please see my query below.

p.limit=-1
p.offset=0
path=/content/dam
type=dam:Asset

1_property=jcr:content/cq:lastReplicationAction_scene7
1_property.value=Activate

2_property=jcr:content/cq:lastReplicationAction
2_property.value=Activate

3_group.or=true

3_group.1_property=jcr:content/metadata/custom:assetType

3_group.1_property.1_value=image

3_group.1_property.2_value=document

3_group.1_property.1.operation=equals

3_group.1_property.2.operation=equals

4_group.or=true

4_group.1_property.operation=not

4_group.1_property=jcr:content/offTime

4_group.2_daterange.property=jcr:content/offTime

4_group.2_daterange.operation=>=

4_group.2_daterange.lowerBound=1729147820649

orderby.sort=asc

orderby=@jcr:content/jcr:lastModified



The corresponding XPath generated is,

/jcr:root/content/dam//element(*, dam:Asset)
[
((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image'))
and (not(jcr:content/@offtime)
and (jcr:content/@offtime > xs:dateTime('2024-10-17T06:50:20.649Z')))
and (jcr:content/@cQ:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cQ:lastReplicationAction = 'Activate')
]
order by jcr:content/@jcr:lastModified

But what I'm looking is those assets where the offTime doesn't exist or offTime greater than current time, which should be an OR condition. But what I'm getting is an AND condition. How do I solve this?

[MukeshYadav_]

Hi @jezwn ,

You may try below xpath once,

/jcr:root/content/dam//element(*, dam:Asset)
[
((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image'))
and 
(
    not(jcr:content/@offtime) 
    or 
    (jcr:content/@offtime > xs:dateTime('2024-10-17T06:50:20.649Z'))
)
and 
(jcr:content/@cQ:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cQ:lastReplicationAction = 'Activate')
]
order by jcr:content/@jcr:lastModified

Thanks

[jezwn]

The xpath works correctly. But how do I do the same with querybuilder query.

[MukeshYadav_]

You may try below query with minor (order) changed-

p.limit=-1
p.offset=0
path=/content/dam
type=dam:Asset

1_property=jcr:content/cq:lastReplicationAction_scene7
1_property.value=Activate

2_property=jcr:content/cq:lastReplicationAction
2_property.value=Activate

3_group.or=true

3_group.1_property=jcr:content/metadata/custom:assetType
3_group.1_property.1_value=image
3_group.1_property.2_value=document
3_group.1_property.1.operation=equals
3_group.1_property.2.operation=equals

4_group.or=true

4_group.1_property=jcr:content/offTime
4_group.1_property.operation=not

4_group.2_daterange.property=jcr:content/offTime
4_group.2_daterange.operation=>=
4_group.2_daterange.lowerBound=1729147820649

orderby.sort=asc
orderby=@jcr:content/jcr:lastModified

If above works then fine else you may check exist with exists operation as false

p.limit=-1
p.offset=0
path=/content/dam
type=dam:Asset

1_property=jcr:content/cq:lastReplicationAction_scene7
1_property.value=Activate

2_property=jcr:content/cq:lastReplicationAction
2_property.value=Activate

3_group.or=true

3_group.1_property=jcr:content/metadata/custom:assetType
3_group.1_property.1_value=image
3_group.1_property.2_value=document
3_group.1_property.1.operation=equals
3_group.1_property.2.operation=equals

4_group.or=true

4_group.1_property.operation=exists
4_group.1_property=jcr:content/offTime
4_group.1_property.value=false

4_group.2_daterange.property=jcr:content/offTime
4_group.2_daterange.operation=>=
4_group.2_daterange.lowerBound=1729147820649

orderby.sort=asc
orderby=@jcr:content/jcr:lastModified

Thanks

[jezwn]

The first query returns in xpath as:

/jcr:root/content/dam//element(*, dam:Asset)
[
((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image'))
and (not(jcr:content/@offTime)
and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z')))
and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate')
]
order by jcr:content/@jcr:lastModified

And the second as,

/jcr:root/content/dam//element(*, dam:Asset)
[
((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image'))
and (not(jcr:content/@offTime)
and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z')))
and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate')
]
order by jcr:content/@jcr:lastModified

Please note that both are still AND condition.

[MukeshYadav_ · Accepted solution]

Hi @jezwn ,

Please try below one

p.limit=-1
p.offset=0
path=/content/dam
type=dam:Asset

1_property=jcr:content/cq:lastReplicationAction_scene7
1_property.value=Activate

2_property=jcr:content/cq:lastReplicationAction
2_property.value=Activate

3_group.p.or=true

3_group.1_property=jcr:content/metadata/custom:assetType
3_group.1_property.1_value=image
3_group.1_property.2_value=document
3_group.1_property.1.operation=equals
3_group.1_property.2.operation=equals

4_group.p.or=true

4_group.1_property.operation=exists
4_group.1_property=jcr:content/offTime
4_group.1_property.value=false

4_group.2_daterange.property=jcr:content/offTime
4_group.2_daterange.operation=>=
4_group.2_daterange.lowerBound=1729147820649

orderby.sort=asc
orderby=@jcr:content/jcr:lastModified

Use group.p.or=true instead of group.or=true

Thanks

[jezwn]

That worked like a charm. Could you help me understand what difference does the p make? Thanks

[MukeshYadav_]

Hi @jezwn ,

Correct query syntax is using p.or only as earlier it wasn't as per syntax hence default operation (and) was getting applied.

 https://experienceleague.adobe.com/en/docs/experience-manager-65/content/implementing/developing/pla...

https://github.com/paulrohrbeck/aem-links/blob/master/querybuilder_cheatsheet.md#query-syntax

Thanks