CODEWITHSUNDEEP
sqlpostgresqlsubquery
tim_xyz
tim_xyz

Reputation: 13491

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

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.

Upvotes: 2

Views: 2370

Answers (1)

teppic
teppic

Reputation: 7286

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

Upvotes: 2

Related Questions