nvm, I think I found my answer in code[1]: Yes, all values in the MV column (array) are taken into account. It would be interesting to be able to filter at that level as well. Ex. an equality check to count only elements in the column equal to an input value:

COUNTMATCHMV(my_column, "foo")

where a string MV column containing:

["foo", "bar", "foo", "baz"]

might return 2. Thoughts on the feasibility? [1] https://github.com/apache/pinot/blob/f8c7e1fc8603f4091e418f3841dcb6bc2d75d5d8/pino[…]core/query/aggregation/function/CountMVAggregationFunction.java

Checkout

ValueIn

transform function: https://docs.pinot.apache.org/users/user-guide-query/supported-transformations

User should use

HAVING

to filter the aggregations (or post-aggregation transform results)

I found what I need by combining

ValueIn

and

CountMV

. Using the airline stats quick start example I could run:

select
  AirlineID,
  countmv(valuein(DivAirports, 'ORD')) as ORD

from
  airlineStats


GROUP BY AirlineID

limit 10

to count instances of

ORD

in DivAirports, per Airline. I could also add

HAVING ORD > 0

as per Jackie’s suggestion. Thanks for the help folks!

👍