Set limit to array_agg()

Question

I have the following Postgres query:

SELECT array_agg("Esns".id ) 
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2 
LIMIT 2;

The limit will affect the rows. I want it to limit the array_agg() to 2 items. The following query works but I get my output with each entry in quotes:

SELECT array_agg ("temp")  
FROM (
    SELECT "Esns".id 
    FROM public."Esns", 
         public."PurchaseOrderItems" 
    WHERE 
        "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
        AND "PurchaseOrderItems"."GradeId"=2 
    LIMIT 4
) as "temp" ;

This give me the following output

{(13),(14),(15),(12)}

Any ideas?

cdalxndr · Accepted Answer · 2019-12-26 19:33:34Z

30

select id[1], id[2]
from (
    SELECT array_agg("Esns".id ) as id
    FROM public."Esns", 
         public."PurchaseOrderItems" 
    WHERE 
        "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
        AND "PurchaseOrderItems"."GradeId"=2 
) s

or if you want the output as array you can slice it:

SELECT (array_agg("Esns".id ))[1:2] as id_array
FROM public."Esns", 
     public."PurchaseOrderItems" 
WHERE 
    "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id 
    AND "PurchaseOrderItems"."GradeId"=2

edited Dec 26, 2019 at 19:33

cdalxndr

1,5601 gold badge17 silver badges24 bronze badges

answered Sep 19, 2012 at 15:35

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1175817 Over a year ago

Perfect. Thanks. The second answer is what i was looking for as i do want an array and i want to be able to dynamically set the upper bound on the limit. Thank you.

Erwin Brandstetter · Accepted Answer · 2020-05-31 13:00:11Z

6

The parentheses (not "quotes") in the result are decorators for the row literals. You are building an array of whole rows (which happen to contain only a single column). Instead, aggregate only the column.

Also, direct array construction from a query result is typically simpler and faster:

SELECT ARRAY (
   SELECT e.id 
   FROM   public."Esns" e
   JOIN   public."PurchaseOrderItems" p ON p.id = e."PurchaseOrderItemId"
   WHERE  p."GradeId" = 2 
   --  ORDER BY ???
   LIMIT  4  -- or 2?
   )

You need to ORDER BY something if you want a stable result and / or pick certain rows. Otherwise the result is arbitrary and can change with every next call.

While being at it I rewrote the query with explicit JOIN syntax, which is generally preferable, and used table aliases to simplify.

edited May 31, 2020 at 13:00

answered Sep 19, 2012 at 17:32

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

2 Comments

user779159 Over a year ago

Are there cases where array constructor won't work and array_agg is needed? Constructor is able to replace all uses of array_agg that I have. Is there an equivalent constructor for json that could simplify or replace json_agg?

Erwin Brandstetter Over a year ago

@user779159: Yes: Multiple array aggregations in the same SELECT list, Possibly with differing per-aggregate sort order.) JSON: no, but you can use ` to_jsonb(ARRAY(...))`.

Collectives™ on Stack Overflow

Set limit to array_agg()

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related