Postgresql – Combining array_agg and unnest

aggregatearraypostgresqlpostgresql-10

Given a dataset (with a GIN index on values):

key | values
-------------
 1  | {4,2,1}
 1  | {2,5}
 2  | {4,1,3}

I want to aggregate the arrays:

key | values
-------------
 1  | {4,2,1,5}
 2  | {4,1,3}

My first thought didn't work:

SELECT key, array_agg(DISTINCT unnest(values)) AS values FROM data GROUP BY key

[0A000] ERROR: aggregate function calls cannot contain set-returning function calls
Hint: You might be able to move the set-returning function into a LATERAL FROM item.

Not being familiar with LATERAL FROM, it's not obvious to me how to achieve the desired output.

Best Answer

You need to do the unnest in a sub-query:

select d."key", array_agg(distinct x.v) 
from data d
  cross join lateral unnest(d."values") as x(v)
group by d."key";

Set returning functions (like unnest()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.

from data cross join lateral unnest(...) is an explicit way of writing from data, unnest(...) which also generates a cross join. But I prefer the explicit cross join operator to document that I indeed intended to write a cross join, rather than accidentally.

This will however not preserve the order of the elements.

Online example: https://rextester.com/TVIDB57711

Related Solutions

Mysql – Should I normalize the string arrays, or just keep it a simple string

A couple of things you might want to consider:

Maintenance of the data.

If the data is going to change often then it would be easier and quicker to have the data normalised so you only have to change it in one place and have all the usages of it automatically update. Conversely, if the data hardly ever changes then this is not a consideration.
Full text searching.

This should be quicker than doing like '%string%' searches and, depending on your database and what's available, may dictate the format you have store your data in.

In addition there's the obvious thing that storing the text repeatedly in the table(s) will increase the size of your database. This may have an impact on performance or cost you more to make sure you have enough disk space and/or memory available on your server.

Wikipedia has an article on the basics. It's not an area I'm terribly familiar with, just that a colleague is looking into this and there are trade-offs with how the data is organised when doing other searches. Other links - MSDN, MySQL and PostgreSQL

Postgresql – Select into specific array positions with array_agg()

Your answer basically gets the job done:

SELECT b.id, array_agg(b.stock) AS stock
FROM  (
   SELECT i.id, COALESCE(i_s.stock, 0) AS stock
   FROM   item i
   CROSS  JOIN unnest('{1,2}'::int[]) n
   LEFT   JOIN item_stock i_s ON i.id = i_s.item_id AND n.n = i_s.shop_id
   ORDER  BY i.id, n.n
   ) b
GROUP  BY b.id;

Two notable changes:

Order is not guaranteed without ORDER BY in the subquery or as additional clause to array_aggregate() (typically more expensive). And that's the core element of your question.
unnest('{1,2}'::int[]) instead of generate_series(1,2) as requested shop IDs will hardly be sequential all the time.

I also moved the set-returning function from the SELECT list to a separate table expression attached with CROSS JOIN. Standard SQL form, but that's just a matter of clarity and taste, not a necessity. At least in Postgres 10 or later. See:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Doing the same with LEFT JOIN LATERAL and an ARRAY constructor might be a bit faster as we don't need the outer GROUP BY and the ARRAY constructor is typically faster, too:

SELECT i.id, s.stock
FROM   item i
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT COALESCE(i_s.stock, 0)
      FROM   unnest('{1,2}'::int[]) n
      LEFT   JOIN item_stock i_s ON i_s.shop_id = n.n
                                AND i_s.item_id = i.id
      ORDER  BY n.n
      ) AS stock
   ) s;

And if you have more than just the two shops, a nested crosstab() should provide top performance:

SELECT i.id, COALESCE(stock, '{0,0}') AS stock
FROM   item i
LEFT   JOIN (
   SELECT id, ARRAY[COALESCE(shop1, 0), COALESCE(shop2, 0)] AS stock
   FROM   crosstab(
     $$SELECT item_id, shop_id, stock
       FROM   item_stock
       WHERE  shop_id = ANY ('{1,2}'::int[])
       ORDER  BY 1,2$$

     , $$SELECT unnest('{1,2}'::int[])$$
      ) AS ct (id int, shop1 int, shop2 int)
   ) i_s USING (id);

Needs to be adapted in more places to cater for different shop IDs.

PostgreSQL Crosstab Query

db<>fiddle here

Index

Make sure you have at least an index on item_stock (shop_id, item_id) - typically provided by a PRIMARY KEY on those columns. For the crosstab query, it also matters that shop_id comes first. See:

Is a composite index also good for queries on the first field?

Adding stock as another index expression may allow faster index-only scans. In Postgres 11 or later consider an INCLUDE item to the PK like so:

PRIMARY KEY (shop_id, item_id) INCLUDE (stock)

But only if you need it a lot, as it makes the index a bit bigger and possibly more susceptible to bloat from updates.

Best Answer

Related Solutions

Mysql – Should I normalize the string arrays, or just keep it a simple string

Postgresql – Select into specific array positions with array_agg()

Index

Related Question