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
BedrockMission!

Learn More

View all

Sign in to view all badges

Can Query Builder search on multi-value property NOT containing a value?

Avatar

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile
EricES
Level 1

26-09-2018

Is there a way to use Query Builder to find a multi-value property that does NOT containt a particular value?

For instance, we have a "language" property that is a multi-value String. Can Query Builder find a node where the language property does not contain 'en' (English)?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile
Arun_Patidar
MVP

26-09-2018

Hi,

You got confused between 'exists' and 'not'

property.operation :

equals” for exact match (default),

unequals” for unequality comparison,

like” for using the jcr:like xpath function ,

not” for no match ,

exists” for existence match

Answers (10)

Answers (10)

Avatar

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile
EricES
Level 1

28-09-2018

Thanks for your help, Arun!

Avatar

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile
Arun_Patidar
MVP

27-09-2018

Hi EricES

Yes, It is not giving results as expected using QueryBuilder but with XPATH it does.

I would suggest you can go with SQL2 query instead of Query Builder.

The last XPATH query, I've shared which gives result when there is no language property or no 'en' value for language property.

If you want to check both conditions just put one more condition with AND.

/jcr:root/content/dam/subfolder//element(*, nt:unstructured)

[

@language and not(jcr:like(@language, 'en'))

]

Avatar

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile
EricES
Level 1

27-09-2018

Arun - Your XPath example helped me understand why we were getting different results.

Though I'm working on a solution that uses Query Builder via the Java API, I tested this through the Query Builder Debugger. When I put these parameters into the Debugger:

path=/content/dam/subfolder

type=nt:unstructured

property=language

property.value=en

property.operation=not

here is the XPath that it generates:

/jcr:root/content/dam/subfolder//element(*, nt:unstructured)

[

not(@language)

]

Notice that it ignored "property.value".

However, when I add the additional "jcr:like" from your XPath example above and run the modified XPath query in the CRX Query tool:

not(jcr:like(@language, 'en'))

it works as expected. While it still returns nodes without the "language" property, it also includes nodes that have the "language" property but do not have an "en" value.

I am assuming that the Query Builder Java API behaves the same, translating to the same XPath query (or SQL2 equivalent) underneath.

We are using AEM 6.1 SP2. Is it possible that Query Builder in this version behaves differently than in newer version?

Avatar

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile
Arun_Patidar
MVP

26-09-2018

you can try with XPATH queries as well

/jcr:root/content/AEM63App/fr//element(*, cq:Page)

[

(not(jcr:like(jcr:content/@language, 'en')))

]

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

26-09-2018

Nice response Arun!

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

26-09-2018

Just tested Arun;s response - this compiles --

Just tested this - it compiles and works to query We Retail pages using QueryBuilder.

             Map<String, String> map = new HashMap<String, String>();

             

             //set QueryBuilder search criteria                  

             map.put("type", "cq:Page");

             map.put("path", "/content/we-retail");

             map.put("property", "jcr:content/jcr:language");

             map.put("property.value", "en_CA");

             map.put("property.operation", "not");

Avatar

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile
EricES
Level 1

26-09-2018

Arun - That seems to show nodes that don't have the "language" property at all. I believe that when "not" is used for "property.operation", the "property.value" predicate is ignored.

Avatar

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile

Avatar
Coach
MVP
Arun_Patidar
MVP

Likes

1,279 likes

Total Posts

3,153 posts

Correct Reply

889 solutions
Top badges earned
Coach
Contributor 2
Ignite 10
Give Back 700
Boost 1000
View profile
Arun_Patidar
MVP

26-09-2018

Hi,

Try below

property=language

property.value=en

property.operation=not

E.g.

path=/content/AEM63App/fr

type=cq:Page

property=jcr:content/language

property.value=en

property.operation=not

Avatar

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile

Avatar
Validate 1
Level 1
EricES
Level 1

Likes

2 likes

Total Posts

13 posts

Correct Reply

0 solutions
Top badges earned
Validate 1
Ignite 1
Boost 1
View profile
EricES
Level 1

26-09-2018

In hoping to find an undocumented predicate feature, I experimented with using multiple "operation" properties for "property". For example:

property=language

property.value=en

property.1_operation=not

property.2_operation=like

This didn't seem to work.

Avatar

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile

Avatar
Validate 25
Level 10
smacdonald2008
Level 10

Likes

1,406 likes

Total Posts

12,671 posts

Correct Reply

2,278 solutions
Top badges earned
Validate 25
Validate 10
Validate 1
Give back 900
Give back 600
View profile
smacdonald2008
Level 10

26-09-2018

I have personally never written a query to exclude values like this. Check this for possible help -- Query Builder Predicate Reference