Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

querybuilder search on Property case insensitive

Avatar

Level 2

Hi,

I have seen a similar question asked but never found a definitive answer. If I want to query a bunch of nodes for a particular property value using the "like" operation. Is there a way to make that query case insensitive?

appreciate any help.

 

Sample Query  that is case sensitive:

path=/content/project/en type=cq:Page 1_orderby=@jcr:content/date 1_orderby.sort=desc 3_property=jcr:content/cq:template 3_property.value=/apps/project/templates/person 3_property=jcr:content/jcr:title 4_property.1_value = %nameFragment% 4_property.operation = like

Best,

Dmitry

1 Accepted Solution

Avatar

Correct answer by
Employee

Hi Dmitry,

The trick is to lower case the property

4_property=fn:lower-case(jcr:content/jcr:title)

and then lower case the search term

4_property.1_value = %namefragment%

Regards,

Justin

View solution in original post

11 Replies

Avatar

Level 10

When working with the QueryBuilder, it is strongly recommended that you use the QueryBuilder debugger tool. You can perform different tests and see what works and what does not. For more information, see How to Use CQ’s Query Debugger Tool

Avatar

Level 2

Thanks Scott, the search debugger tool is very helpful and so is the Query Editor that is part of the AEM tools put out by Professional Services. 

The query itself is perfectly fine and produces the required results. However the querying is case sensitive and I would like to ignore case in this instance. Is there a way to achieve this using the querybuilder API? Or does this require a custom predicate? 

If the latter, has anyone seen a similar example in the wild?

Avatar

Correct answer by
Employee

Hi Dmitry,

The trick is to lower case the property

4_property=fn:lower-case(jcr:content/jcr:title)

and then lower case the search term

4_property.1_value = %namefragment%

Regards,

Justin

Avatar

Level 2

Thanks Justin, that did the trick.

Avatar

Employee

I don't get this to work in http://localhost:4502/libs/cq/search/content/querydebug.html

Using this:

path=/content/we-retail

type=cq:Page

1_property=fn:lower-case(jcr:content/jcr:title)

1_property.value=english

Avatar

Level 2

yeah it doesn seem to work via property map, though xpath works

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

[(fn:lower-case(jcr:content/@jcr:title) = 'hi') ]

There is additonal support in oak index definitions to run such query optimally by defining function indexes [OAK-3574] Query engine: support p=lowercase('x') and other function-based indexes - ASF JIRA

Avatar

Level 2

Hi ,

I am  trying to  add the fn:lower-case(jcr:content/jcr:title) in my querybuilder code in my servlet  but lowercase function doesnt work.

When QueryBuilder creates a Xpath query it resolves to below :

fn:lower-case_x0028__x0040_jcr:content/@boxTitle_x0029_

Has anyone faced this issue?

Avatar

Employee

farahz913310​ Based on the discussion in Re: AEM 6.2 "fn:lowercase" is not working   (and some other linked threads), it appears that the function described in my original "correct" answer (from 2015) stopped working with AEM 6.2 and was never officially supported. That thread has a proposed solution which does (apparently) work in 6.2 and beyond.

Avatar

Level 2

Thanks @justin.

As mentioned here Re: AEM 6.2  "fn:lowercase"  is not working ,So the better solution is use SQL2 instead of Xpath query ?

Avatar

Employee

As I understand it, the issue is not really XPath vs. SQL2, but really with the QueryBuilder. But you might want to ask in the other thread to be sure.

Avatar

Level 8

Hi,

Can you take a look at the below post and see if that helps.

AEM 6.2 "fn:lowercase" is not working