Returning SELECT results from a function as a JSON (Postgres)

Question

I have a plpgsql function and a complex nested SELECT query and I'd like to return the result table of the query as a JSON. How would I go about doing this? I have something like this:

CREATE TEMPORARY TABLE selectRESULTS AS
 /*some long, complicated, and nested SELECT query
 .............
 ..............
 */
RETURN array_to_json(array_agg(selectResults));

---

Edit: I added additional specifications after trying one of the solutions

Using Pozs' method:

RETURN (SELECT json_agg(selectResults) FROM selectResults);

I do get a json. However it's nested with extra arrays which makes the inner object harder to access.

Currently I get

[['[{"someKey": someValue}, etc]']]

Is there any way to get rid of the two outer arrays so that instead of using

somejson[0][0][0]["someKey"] to acess someValue, I can just use someJson[0]["someKey"]?

Thanks in advance!

Answer 1

In short, you have an error in your syntax; you need a SELECT to use aggregate functions, like array_agg. You can use the array() constructor too:

-- do NOT use these, these are far from optimal

RETURN array_to_json(array(SELECT selectResults FROM selectResults));
-- or
RETURN (SELECT array_to_json(array_agg(selectResults)) FROM selectResults);

But json_agg(...) does effectively the same as array_to_json(array_agg(...)) but faster:

RETURN (SELECT json_agg(selectResults) FROM selectResults);

Note: I used selectResults to select the whole row of selectResults table, but if you have only 1 column in it, you can use directly that.

However, you may not need a temporary table at all. If your logic is that simple, you can use a simple sql function, like:

create function xyz(p1_type, ..., pn_type)
  returns json
  language sql
as $func$
  select json_agg(select_results)
  from (
    /*some SELECT query here
     .............
     use $1 ... $<n> for parameters here,
     or you can name them also within sql functions
     .............
     */
  ) select_results
$func$;