Set limit to array_agg()
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_arrayFROM public."Esns", public."PurchaseOrderItems" WHERE "Esns"."PurchaseOrderItemId" = "PurchaseOrderItems".id AND "PurchaseOrderItems"."GradeId"=2
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.