JCR SQL2 Query or Query builder predicate

[srikanth532]

Need a jcr sql2 query for acs commons report or query builder predicate to get the list of pages which don't have specific cq:tags
Our tags hierarchy :

and the node structure is jcr:content/metadata/cq:tags

Here i want all the pages which do not have dsc:device or dsc:keywords tags

[Magicr]

SELECT * FROM [cq:PageContent] AS page WHERE ISDESCENDANTNODE(page ,"/search/in/path") 
AND NOT (page.[cq:tags] like "dsc:keywords%" or page.[cq:tags] like "dsc:device%")

Due to the fact that the information is saved in jcr:content node, you have to work with this resource.

[srikanth532]

I tried the above query in acs-commons it works fine but instead of giving directly like %dsc:device% we need to get that value from dropdown(search parameter). I tried like '{{tags}}' to get the value(dsc:device) from the dropdown but it didn't work.

if possible, can this be achieved in query builder predicate

[Magicr · Accepted solution]

I don't understand what your requirements exactly are. As far as I understand your prictures -whose shows your structure- the marked tags are a part of full path from others. So there is no other way. May be the keyword 'contains' allows you to avoid der percent signs. My above query would looks like:

SELECT * FROM [dam:AssetContent] AS page WHERE ISDESCENDANTNODE(page ,"/search/in/path") 
AND NOT (Contains(page.[cq:tags], 'dsc:keywords') or Contains(page.[cq:tags], 'dsc:device'))

The query builder pendant for my both queries is:

path=/content/dam/xxx
1_group.1_property=jcr:primaryType
1_group.1_property.value=dam:AssetContent
2_group.1_property=cq:tags
2_group.1_property.value=dsc:keywords%
2_group.1_property.operation=like
2_group.2_property=cq:tags
2_group.2_property.value=dsc:devices%
2_group.2_property.operation=like
2_group.p.or=true
2_group.p.not=true
orderby=path