[druid-user] Is it possible to case-when filtering in json-based queries?

you can get an equivalent using a filtered aggregator.

Refer filtered aggregator here http://druid.io/docs/latest/querying/aggregations

@Nishant, can you offer a code example?

If you are using druid-sql you can prefix the sql query with ‘EXPLAIN PLAN FOR’ to see the native Druid query that would be run, http://druid.io/docs/latest/querying/sql.

Running the example you posted through the planner, I see the native query produced as something like this:

{

“queryType”: “timeseries”,

“dataSource”: {

“type”: “table”,

“name”: “EMP”

},

“intervals”: {

“type”: “intervals”,

“intervals”: [

“-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z”

]

},

“descending”: false,

“virtualColumns”: ,

“filter”: {

“type”: “in”,

“dimension”: “A”,

“values”: [

“a1”,

“a2”

],

“extractionFn”: null

},

“granularity”: {

“type”: “all”

},

“aggregations”: [

{

“type”: “filtered”,

“aggregator”: {

“type”: “count”,

“name”: “a0”

},

“filter”: {

“type”: “and”,

“fields”: [

{

“type”: “not”,

“field”: {

“type”: “selector”,

“dimension”: “u_id”,

“value”: null,

“extractionFn”: null

}

},

{

“type”: “selector”,

“dimension”: “A”,

“value”: “a1”,

“extractionFn”: null

}

]

},

“name”: “a0”

},

{

“type”: “filtered”,

“aggregator”: {

“type”: “count”,

“name”: “a1”

},

“filter”: {

“type”: “and”,

“fields”: [

{

“type”: “not”,

“field”: {

“type”: “selector”,

“dimension”: “u_id”,

“value”: null,

“extractionFn”: null

}

},

{

“type”: “selector”,

“dimension”: “A”,

“value”: “a2”,

“extractionFn”: null

}

]

},

“name”: “a1”

}

],

“postAggregations”: ,

“limit”: 2147483647,

“context”: {

“defaultTimeout”: 300000,

“maxScatterGatherBytes”: 9223372036854775807,

“skipEmptyBuckets”: true,

“sqlCurrentTimestamp”: “2000-01-01T00:00:00Z”,

“sqlQueryId”: “dc38974a-a711-46a4-89b3-7bf351248bc6”

}

}