Re: [druid-user] How to use "filtered dimension spec" in Druid SQL?

Hi Jason -

Can you share a sample query that returns the other values (not selected for)? I tried a few queries with GROUP BY and an mvd, and druid sql seemed to do the filtering for me. (I notice the doc page you referenced mentioned using HAVING, maybe that’s the key.)

Hey Jason,
AFAIK, unfortunately, listFiltered is not supported in Druid SQL, only in native queries (though I’d love to be proven wrong here :wink:).

Itai

I see now, that’s frustrating. I tried WHERE, HAVING, using a subquery and selecting from it using WHERE, etc, no luck.
The only way I found is to kind of unfold the values first, and you have to hardcode for each value. In your case, you’d end up with a 33-way UNION query, and long unions are slow. But, if you want to try, this worked for my dataset, with an mvd called ‘d6_mvd’ and a couple of values from a couple of different mvds. (I used ordinal instead of offset, because otherwise rows that didn’t have the value came back as null, and I couldn’t filter them out from rows that did have the value at offset 0 - unless you set useDefaultValueForNull, maybe.)

select array_ordinal(d6_mvd,array_ordinal_of(d6_mvd,‘beca’)) myVal, count()
from dataTest
where array_ordinal_of(d6_mvd, ‘beca’) > 0
group by 1
UNION ALL
select array_ordinal(d6_mvd,array_ordinal_of(d6_mvd,‘delo’)) myVal, count(
)
from dataTest
where array_ordinal_of(d6_mvd, ‘delo’) > 0
group by 1

(I’m selecting counts where the mvd contains ‘beca’ or ‘delo’, and excluding counts for the other values in those same mvds from the output. Pretty ugly, though.)