How to sort aggregate function call

Question

If I have an expensive function call that takes a JSONB and returns an INT, e.g.

CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS

How do I go about ordering the aggregate result from calling it on a set of JSON data, e.g:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY ???)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I get an error if I try to assign a name to the result:

SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

ERROR:  42601: syntax error at or near "id"
LINE 1: SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)...
                                                 ^
LOCATION:  scanner_yyerror, scan.l:1134

I found I can do it by calling the function twice, but I hate that:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY my_expensive_function(data))
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I also found that this seems to work, but it is a bit cumbersome also:

WITH data_set AS (
    SELECT my_expensive_function(data) AS id
    FROM JSONB_ARRAY_ELEMENTS(json_set) data
) SELECT ARRAY_AGG(id ORDER BY id) FROM data_set;

UPDATE: I should have specified - Is there any magic I can do in a single query or do I have to use WITH/subquery? Why can't I add a name to the result of the function?

Answer 1

Use a subquery:

SELECT array_agg(x ORDER BY x)
FROM (SELECT my_expensive_function(data) AS x
      FROM jsonb_array_elements(json_set) AS data
     ) AS q;

Answer 2

Another option is to invoke my_expensive_function(data) in a LATERAL cross join:

SELECT
  ARRAY_AGG(id ORDER BY id)
FROM
  JSONB_ARRAY_ELEMENTS(json_set) AS data
  CROSS JOIN LATERAL (SELECT my_expensive_function(data)) AS x (id)
;

Alternatively, you can express the cross join using a comma:

SELECT
  ARRAY_AGG(id ORDER BY id)
FROM
  JSONB_ARRAY_ELEMENTS(json_set) AS data,
  LATERAL (SELECT my_expensive_function(data)) AS x (id)
;