Search code examples
sqlarrayspostgresqllimit

Set limit to array_agg()


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?


Solution

  • 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