current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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!

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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$;
share|improve this answer
    
Thanks! I haven't tried this yet but my query is quite complex (heavily nested, performs various calculations, and works with multiple columns) so a simple SQL function may be out of the question. –  Teboto Feb 5 at 22:10
    
Edited my post. Do you happen to have an alternate solution that meets my additional specifications? –  Teboto Feb 6 at 11:51
    
@Teboto it is impossible to tell without any further knowledge about selectResults. (The most possible solution is that your data is already in an array) Please provide more details, and don't change the question after every bit of new information. If you have a new problem, post it as a separate question. –  pozs Feb 6 at 12:00
    
You're right, the problem has to do with my selectResults, which is obtained from subqueries. I'll post a new question, thanks. –  Teboto Feb 6 at 20:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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