Case insensitive filters

Hi,

how can we specify indicate that we would like to ingore the case of any dimension/attribute value in groupBy queries?

For example consider the query below:

{

“queryType”: “groupBy”,
“dataSource”: “xReports”,
“granularity”: “ALL”,
“filter”: { “type”: “selector”, “dimension”: “dept”, “value”: “Accounts” },
“dimensions”: [“dept”],
“aggregations”: [
{ “type”: “longSum”, “name”: “count”, “fieldName”: “views” }
],
“postAggregations”: [

],
“intervals”: [“2019-09-12T00:00:00.000Z/2019-09-14T00:00:00.000Z”]

}

I want to fetch all the data where dept is “accounts” or “Accounts” .

I also have some dimensions with alphanumeric values such as abc45kld-pqe1. Due to some limitation the events with these dimensions could be having the values in capital for example: abc45kld-pqe1 could come in as ABC45KLD-PQE.

Thanks,

Prathamesh

If there are fewer combinations, You can specify them in IN filter as below.

“filter”: {
“type”: “in”,
“dimension”: “dept”,
“values”: [
“Accounts”,
“accounts”]
}

Thanks and Regards,

Vaibhav

Hi Vaiabhav,

Combinations can be multiple thats why i am looking for some way to ignore case while fetching from druid.

Thanks,

Prathamesh

Additionally, Maybe you would like to use UPPER()/LOWER() function in druid sql to filter such strings.

For other available functions , You can refer below link :
http://druid.incubator.apache.org/docs/latest/querying/sql

Preferably, you can use druid SQL and write the query which will be easier to write and then if you want you can concert it JSON

Explain plan will give the native JSON:

Thanks and Regards,

Vaibhav

The following is an excellent reference for query filters:

https://druid.apache.org/docs/latest/querying/filters.html

The following filter should do the trick for you:

{

“filter”: {

“type”: “search”,

“dimension”: “dept”,

“query”: {

“type”: “insensitive_contains”,

“value”: “accounts”

}

}

}

Hi J B,

This works well assuming that the dimension value doesn’t contain any substring which may be common across other events.

Thanks,

Prathamesh