Having filters (groupBy)

    A having clause is a JSON object identifying which rows from a groupBy query should be returned, by specifying conditions on aggregated values.

    It is essentially the equivalent of the HAVING clause in SQL.

    Apache Druid supports the following types of having clauses.

    Query filter HavingSpecs allow all to be used in the Having part of the query.

    The grammar for a query filter HavingSpec is:

    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    5.     "having":
    6.         {
    7.             "type" : "filter",
    8.             "filter" : {
    9.               "type": "selector",
    10.               "dimension" : "<dimension>",
    11.               "value" : "<dimension_value>"
    12.             }
    13.         }
    14. }

    You can use “filter” HavingSpecs to filter on the timestamp of result rows by applying a filter to the “__time” column.

    The simplest having clause is a numeric filter. Numeric filters can be used as the base filters for more complex boolean expressions of filters.

    Here’s an example of a having-clause numeric filter:

    1. {
    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    5.     "having":
    6.         {
    7.             "type": "greaterThan",
    8.             "value": <numeric_value>
    9.         }
    10. }

    Equal To

    The equalTo filter will match rows with a specific aggregate value. The grammar for an equalTo filter is as follows:

    This is the equivalent of HAVING <aggregate> = <value>.

    Greater Than

    1. {
    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    6.         {
    7.             "type": "greaterThan",
    8.             "aggregation": "<aggregate_metric>",
    9.             "value": <numeric_value>
    10.         }
    11. }

    This is the equivalent of HAVING <aggregate> > <value>.

    Less Than

    The lessThan filter will match rows with aggregate values less than the specified value. The grammar for a greaterThan filter is as follows:

    1. {
    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    5.     "having":
    6.         {
    7.             "type": "lessThan",
    8.             "aggregation": "<aggregate_metric>",
    9.             "value": <numeric_value>
    10.         }
    11. }

    This is the equivalent of HAVING <aggregate> < <value>.

    dimSelector

    The dimSelector filter will match rows with dimension values equal to the specified value. The grammar for a dimSelector filter is as follows:

    AND

    The grammar for an AND filter is as follows:

    1. {
    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    5.     "having":
    6.         {
    7.             "type": "and",
    8.                 {
    9.                     "type": "greaterThan",
    11.                     "value": <numeric_value>
    12.                 },
    13.                 {
    14.                     "type": "lessThan",
    15.                     "aggregation": "<aggregate_metric>",
    16.                     "value": <numeric_value>
    17.                 }
    18.             ]
    19.         }
    20. }

    OR

    1. {
    2.     "queryType": "groupBy",
    3.     "dataSource": "sample_datasource",
    4.     ...
    5.     "having":
    6.         {
    7.             "type": "or",
    8.             "havingSpecs": [
    9.                 {
    10.                     "type": "greaterThan",
    11.                     "aggregation": "<aggregate_metric>",
    12.                     "value": <numeric_value>
    13.                 },
    14.                 {
    15.                     "type": "equalTo",
    16.                     "aggregation": "<aggregate_metric>",
    17.                     "value": <numeric_value>
    18.                 }
    19.             ]
    20. }

    NOT

    The grammar for a NOT filter is as follows: