Unnest array of rows with a tagged column

Question

I'm trying to create a query that will return data in this format:

id	time	value
1	1	2.5
1	2	3.5
1	3	6.4
2	1	8.3
2	2	8.5

I'm using Timescaledb and wish to use one of their downsampling functions for each unique id

select
 id,
 asap_smooth(time, value, 80)
from
 data
group by
 id

Where the asap_smooth aggregate function returns a custom datatype that can be unnested without the id into the following format:

SELECT * FROM unnest(
 (SELECT asap_smooth(time, value, 80)
 FROM data));

time	value
1	2.5
2	3.5
3	6.4

Is there anyway to get each row tagged with the id it belongs to? I've tried this query however I can't seem to unpack the record type into their own columns:

select
 id,
 unnest ((select asap_smooth(time, value, 80))) t
from
 data
group by
 id

id	t
1	{1,2.5}
1	{2,3.5}
1	{3,6.4}
2	{1,8.3}
2	{2,8.5}

• Erwin Brandstetter: So do you have your answer?

Answer 1

The type Timevector is unlike other standard data types in Postgres. Similar to an array of <ROW type>, but using a vector instead of the array, which is otherwise only used in the system catalogs.
Simply decomposing the resulting row type should do it:

SELECT id, (unnest(asap_smooth(time, value, 80))).*
FROM data
GROUP BY id;

But I would rather use this more explicit form:

SELECT sub.id, asap_row.*
FROM (
 SELECT id, asap_smooth(time, value, 80) AS asap
 FROM data
 GROUP BY id
 ) sub
LEFT JOIN LATERAL unnest(asap) AS asap_row ON true;

Because the second form preserves rows from the subquery sub where asap_smooth() returns NULL or an empty set. The first form eliminates such rows. See:

• Flattening a relation with an array to emit one row per array entry

And because of this:

• How to avoid multiple function evals with the (func()).* syntax in a query?

Plus, I am more confident the second form actually works.