0

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?

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;
0

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)
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.