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

`ARRAY_CAT` concatenates two arrays. It does not take a single argument, and is not an aggregate function. You want to aggregate all the `lw_top_array` array values? You'll have to use one of your commented out alternatives. — 404, Apr 26 '18 at 16:40
thanks, but when i try the first solution in either way: array_agg((select lw_top_array from unnest(dati1_v.lw_top_array))) OR array_agg(dati1_v.lw_top_array) i get the error: ERROR: could not find array type for data type numeric[] — Fabio, Apr 27 '18 at 10:05
You can't array_agg an array, so it's expected the second one won't work. The first won't work because array_agg doesn't accept a set, which is what that subquery returns. Normally you'll need something like `ARRAY_AGG(u) FROM UNNEST(your_field) u`, but I'll admit I'm not sure what the best course of action is in your case. I'd say you need to unnest it at some point and then array_agg the field, but I don't know what effect that would have on your query - bit difficult to tell with so many subqueries in the SELECT bit. — 404, Apr 27 '18 at 10:28
Basically this is what I'm thinking of: `SELECT id, ARRAY_AGG(ar) FROM ( SELECT g % 2 AS id, UNNEST('{1,2,3,4,5,6,7,8,9,10}'::INTEGER[]) AS ar FROM generate_series(1, 5) g ) x GROUP BY id` You see the level at which I put the UNNEST, under the ARRAY_AGG level. In my case the group by result is the same but as I said, bit difficult for me to tell in your case. — 404, Apr 27 '18 at 10:31
Possible duplicate of [Concatenate/merge array values during grouping/aggregation](https://stackoverflow.com/questions/24153498/concatenate-merge-array-values-during-grouping-aggregation) — JGH, Apr 27 '18 at 12:33
i achieved the desired output in this way: `((string_to_array(string_agg(array_to_string((array(select round((lw -lws.top_min)/(lws.top_max - lws.top_min)*100,2) from unnest(lw_top_array) lw)), ','), ','),','))::numeric[]) AS lw_top_array_tot` - Applying math operations as an inner select, unnesting each subarray of 15 values - transforming it back to an array - transforming each subarray into a string - applying string_agg on the 4 string of 15 values - transforming back the string to array in numeric format — Fabio, May 02 '18 at 08:21
thanks @JGH for the suggestion, however i applied a different solution since i'm in 9.0 — Fabio, May 02 '18 at 08:31

score 0 · Answer 1 · answered Aug 03 '21 at 10:37

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;

score 0 · Answer 2 · answered May 31 '22 at 15:53

0

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

answered May 31 '22 at 15:53

Ryabchenko Alexander

10,057
7
56
88

score 0 · Answer 3 · answered Aug 30 '22 at 15:11

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

concatenate arrays in aggregate query

3 Answers3