concatenate arrays in aggregate query

Question

i have a starting table where there are some meteo data stored every 15 minutes, one field stores leaf wet at 1 minute sampling in a numeric array form, thus i have a 15 values array each row.

Now i want to create a 1 hour aggregation of this table, crating an array of 60 values for this field.

I tried array_cat at first place, but says

array_cat(numeric[]) not existing

the function obviuously exists, so i tought the format was not the one expected, i tried first unnesting and then aggregating, not working again.

Finally i was able to aggregate trough string conversion, but it's not what i wanted (i might in the future apply some numeric elaboration oh that 60-values array)

I paste the query for further investigations

SELECT dati1_v.id_stazione,
       to_char(dati1_v.data_ora, 'YYYY-MM-DD HH24:00:00'::text)   AS date_hour,
       round(avg(dati1_v.temp1_media), 2)                         AS t_avg,
       round(avg(dati1_v.ur1_media), 2)                           AS hum_avg,
       sum(dati1_v.pioggia)                                       AS rain_tot,
       max(dati1_v.pioggia)                                       AS rain_max,
       round((avg((SELECT avg(lw.lw) AS avg FROM unnest(dati1_v.lw_top_array) lw(lw))) - lws.top_min) /
             (lws.top_max - lws.top_min) * 100::numeric, 2)       AS lw_top_avg,
       array_agg((SELECT round((avg(lw.lw) - lws.top_min) / (lws.top_max - lws.top_min) * 100::numeric, 2) AS round
                  FROM unnest(dati1_v.lw_top_array) lw(lw)))      AS lw_top_array,
       array_cat(dati1_v.lw_top_array)                            AS lw_top_array_tot,
-- array_agg((select lw_top_array from unnest(dati1_v.lw_top_array))) AS lw_top_array_tot,
-- array_agg(array_to_string(dati1_v.lw_top_array, ',')) AS lw_top_array_tot,
       round((avg((SELECT avg(lw.lw) AS avg FROM unnest(dati1_v.lw_bottom_array) lw(lw))) - lws.bottom_min) /
             (lws.bottom_max - lws.bottom_min) * 100::numeric, 2) AS lw_bottom_avg,
       array_agg((SELECT round((avg(lw.lw) - lws.bottom_min) / (lws.bottom_max - lws.bottom_min) * 100::numeric,
                               2) AS round
                  FROM unnest(dati1_v.lw_bottom_array) lw(lw)))   AS lw_bottom_array
FROM dati1_v,
     lw_settings lws
WHERE lws.id = 1
GROUP BY dati1_v.id_stazione, to_char(dati1_v.data_ora, 'YYYY-MM-DD HH24:00:00'::text), lws.top_min, lws.top_max,
         lws.bottom_min, lws.bottom_max
ORDER BY dati1_v.id_stazione, to_char(dati1_v.data_ora, 'YYYY-MM-DD HH24:00:00'::text)

in particular, my tries were related to this specific block:

array_cat(dati1_v.lw_top_array)  AS lw_top_array_tot,
-- array_agg((select lw_top_array from unnest(dati1_v.lw_top_array))) AS lw_top_array_tot,
-- array_agg(array_to_string(dati1_v.lw_top_array, ',')) AS lw_top_array_tot

Thanks

Answer 1

For me in similar case helped UNNEST in subquery and ARRAY_AGG of unnnested

            SELECT
                ARRAY_AGG(
                    DISTINCT lw_top
                ) as lw_top_array
            FROM (
                SELECT 
                   UNNEST(lw_top_array) AS lw_top
                  FROM
                    dati1_v
            ) as tmp;

Answer 2

for me helped next query

SELECT 
  my_table.key,
  array_agg(_unnested.item) as array_coll
from my_table
   left join LATERAL (SELECT unnest(my_table.array_coll) as item) _unnested ON TRUE
GROUP by my_table.key

Answer 3

In PostgreSQL, the Group_concat function is not available but you can get similar result as string_agg and array_to_string.

string_agg(array_to_string(file_ids, ','), ',') filter ( where file_ids notnull ) AS file_ids_str

array_to_string and array_to_string works in next way

array_to_string([1, 2, 456], ',') => '1,2,456'
string_agg(['a', 'ab'], ',') => 'a,ab'

the only problem is that result is string with ',' as separator