querybuilder search on Property case insensitive

[Dterner]

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

[smacdonald2008]

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. 

[Dterner]

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?

[justin_at_adobe · Accepted solution]

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

[Dterner]

Thanks Justin, that did the trick.

[Feike_Visser1]

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

[amalhotr]

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

[farahz913310]

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?

[justin_at_adobe]

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.

[farahz913310]

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 ?

[justin_at_adobe]

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.

[cquser1]

Hi,

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

AEM 6.2 "fn:lowercase" is not working