CODEWITHSUNDEEP
postgresql
moonshot
moonshot

Reputation: 699

Array_agg integer arrays of different lengths in postgres 9.5

I'm trying to aggregate integer arrays that have different lengths. This answer pads with nulls and I am trying to incorporate it into my query.

Here is my data:

id   | col
-----+---------
1    | {5,3}
1    | {6,4}
2    | {3}
2    | {2,3}

Here is my desired result

    id   | col
    -----+---------
    1    | {{5,3},{6,4}}
    2    | {{3,NULL},{2,3}}

See DBFiddle

This is my current query, which aggregrates by id but it outputs duplicates. This is my first time using LATERAL function so don't know if this is right approach. 

SELECT 
array_agg(
          array_cat(
            col,
            array_fill(NULL::smallint, ARRAY[lat-COALESCE(array_length(col, 1),0)])
          )
       ) AS result
,tab.id
FROM tab
,LATERAL (SELECT id,MAX(array_length(col,1)) AS lat
          FROM tab GROUP BY id) s
GROUP BY tab.id

Upvotes: 1

Views: 1668

Answers (1)

404
404

Reputation: 8582

How about a slightly different approach:

SELECT id, ARRAY_AGG(CASE WHEN ARRAY_LENGTH(col, 1) < max_length THEN (col || ARRAY_FILL(NULL::SMALLINT, ARRAY[max_length]))[1:max_length] ELSE col END) AS result
FROM tab, (SELECT MAX(ARRAY_LENGTH(col, 1)) AS max_length FROM tab) m
GROUP BY id

If the length is less than the max array length, append an array of max length, then take a "slice" of max length; as a result it works like OVERLAY does for strings.

https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=56b135d87dd919e84ca43daeaf2b081c

Or slightly differently:

SELECT id, ARRAY_AGG(CASE WHEN ARRAY_LENGTH(col, 1) < max_length THEN col || ARRAY_FILL(NULL::SMALLINT, ARRAY[max_length - ARRAY_LENGTH(col, 1)]) ELSE col END) AS result
FROM tab, (SELECT MAX(ARRAY_LENGTH(col, 1)) AS max_length FROM tab) m
GROUP BY id

Only add the required number of NULLs to the array.

https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=22e86b81131a68844206338bf4428dab

Upvotes: 3

Related Questions