Hey folks, is there currently any transform (or other operator) that can be used to “fan-out” from 1 doc/row to many at query time? Effectively, query-time multi-value generation. An example use case would be user sessions which span multiple intervals of time, where the desired query outcome would be to compute average concurrent user sessions per time interval. Can this be computed if the rows contain session start time, end time, and duration, where a user session could exist in many time intervals due to its duration?

Not yet, but with multistage we will have windows functions

In the case I’m contemplating, the time interval would be fixed and known ahead of time (always 30 seconds). So in that case, I believe the desired query could be solved if the 30s intervals were computed and stored as an MV column since that would allow for the “fanout” effect that I’m after for aggs/grouping. Is there a way to create the MV-like value at query-time? Could a transform be written to do so if it does not exist today?

this is the unnest functionality.. we should be able to add this after join is done

From what I’ve seen this already works for MV columns. Ex. If I have a single row with columns

_id: 1

and

_mv: [“a”, “b”, “c”]

, I can run a query like this and get 3 rows returned:

SELECT _id, _mv, count(*) where _id=1 group by _id, _mv

I’ll get a result table like:

_id  _mv  count(*)
1    a    1
1    b    1
1    c    1

It seems technically feasible to have a transform function that maps from SV to MV, or rather having an array output for every scalar input. I’ll play around a bit