Nest a select statement inside array_to_json(array_agg(row_to_json())) in PostgreSQL

Question

I'm trying to understand why this works:

select
    array_to_json(array_agg(row_to_json(positions_table)))
from (
    select
        sum(start_month),
        function_id,
        profile_id
    from positions as subquery
    group by function_id, profile_id
) as positions_table

But this does not:

select
    profiles.id,
    (array_to_json(array_agg(row_to_json(
        select
            sum(start_month),
            function_id,
            profile_id
        from positions as subquery
        group by function_id, profile_id
    ))))
from profiles

It seems like I'm not allowed to put the select... statement inside array_to_json(array_agg(row_to_json())) and that it needs to reference a table instead.

But I'm suspicious I might be missing something.

The error is syntax error near select.

FYI: instead of doing 3 data conversions (json, array, json) you should use jsonb_agg() instead for your wishful result see more. Also, row_to_json expects record as input, subquery is not a record it is set of records, so you can't expect function that's purpose is to convert 1 single row to json to convert N rows. — Kristo Mägi
– Kristo Mägi, Commented Oct 18, 2017 at 0:36

teppic · Accepted Answer · 2017-10-18 03:29:25Z

You could fix your syntax error by wrapping your sub-select in brackets, but then you'd see your second error; postgres would report:

subquery must return only one column

If you fixed that error you'd see:

column "profiles.id" must appear in the GROUP BY clause or be used in an aggregate function

...which is getting closer to the real cause of your problem. Adding GROUP BY id will get postgres to report the root cause:

more than one row returned by a subquery used as an expression

The function row_to_json is expecting a single row.

To aggregate your json summaries by profile id you'll have to perform a join.

SELECT
  pr.id,
  array_to_json(array_agg(row_to_json(positions_table.*)))
FROM profiles pr JOIN (
  SELECT sum(start_month), function_id, profile_id
  FROM positions
  GROUP BY function_id, profile_id
) positions_table ON positions_table.profile_id = pr.id
GROUP BY pr.id

(You can drop the .* from positions_table.*, I just added it for clarity).

Collectives™ on Stack Overflow

Nest a select statement inside array_to_json(array_agg(row_to_json())) in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related