Allow using sub-selects in continuous aggregates including lateral joins

[hedayat]

Sometimes, you need to do a lateral join or a subselect to make a proper aggregation query, e.g. when using unnest() or json_array_elements() or something like that is needed to expand a row to multiple rows. Currently, continuous aggregate query form is so limited that it is impossible to create a CA with a lateral join. It'd be great if this feature is added.

A sample example with sub-select (not a real example, just to show the desired format):

select val, sum(count) count FROM (select (jsonb_array_elements(metadata#>'{sample_data_array}'))::int as count,
jsonb_array_elements(metadata#>'{sample_value}') as val from t1) t group by val
order by count desc limit 10

[luveti]

This would be awesome. I'm storing some data as JSON arrays and wasn't sure what the query would be to denormalize it all for use in a view, so I opened a question on stack overflow here https://stackoverflow.com/q/65280697/1563399. But sadly it uses FILTER.. which isn't supported yet. I modified it to use CASE instead, but of course it was never going to work anyways due to lateral joins not being supported.

Until this is supported, I'm going to have to denormalize the arrays into a second table (95 columns wide!) and do the continuous aggregation on that instead.