Repository navigation
Support exists / notExists filter operators for set-membership filtering #11952
Description
Is your feature request related to a problem? Please describe.
Right now there's no clean way to filter one cube based on whether a related
row exists in another cube, without going through a measure.
Example: I want revenue for all customers who opened a support ticket in the
last 30 days.
SELECT SUM(amount) FROM revenue
WHERE customer_id IN (
SELECT customer_id FROM tickets WHERE created_at > current_date - 30
)
The closest thing Cube has is subquery dimensions (sub_query: true), but
those require the subquery to resolve to a measure/aggregation. So to check
"does this customer have a ticket" you end up defining a ticket_count
measure on the tickets cube and filtering ticket_count > 0, just to fake
an existence check. That's a measure that doesn't mean anything on its own,
it only exists to be compared to zero.
It also means every "does X have a related Y" question requires a
predefined dimension in the model ahead of time. There's no way to do this
ad hoc at query time the way you could with a normal filter.
Describe the solution you'd like
A new pair of filter operators, exists and notExists, where the filter
value is a nested query instead of a list of values:
{
"member": "customers.id",
"operator": "exists",
"query": {
"dimensions": ["tickets.customer_id"],
"filters": [
{ "member": "tickets.created_at", "operator": "inDateRange", "values": ["last 30 days"] }
]
}
}
This would compile to a plain WHERE customers.id IN (SELECT tickets.customer_id FROM tickets WHERE ...) (or EXISTS (...) with a correlated condition,
whichever fits the planner better), with no aggregation or GROUP BY
involved. notExists would be the negated version.
Describe alternatives you've considered
Subquery dimensions, as described above. They work but force you to model
an aggregate measure for something that isn't really an aggregation, and
they're fixed at data-model time rather than usable ad hoc in a query.
Additional context
Happy to put together a PR for this if the general direction is OK.
Activity
john9806 commentedon Sep 21, 2026
I looked into what it would take to support this on the Tesseract (Rust) planner, since that's the default now and the legacy JS planner is on its way out. Wanted to share the approach in case it's useful, and to get a sanity check before writing actual code.
The gap: the wire format that feeds the Rust planner (FilterItem in cube_bridge/base_query_options.rs) only carries scalar filter values — there's no slot for a nested query. So exists/notExists would need a new field there, e.g. query: Option<...>, similar in spirit to how or/and already nest FilterItem recursively.
The part I was less sure about: can you actually plan a query-inside-a-query without breaking param handling? Turns out yes, and it's a pattern that already half-exists in the codebase:
So the shape of a solution would be:
One thing this first pass wouldn't handle: pre-aggregation matching for the nested subquery — it'd just always hit the base SQL. That seems like a reasonable v1 limitation to call out explicitly rather than solve upfront.
Happy to take a stab at this if the general direction sounds right to maintainers — mostly wanted to check the approach before sinking time into it.