CODEWITHSUNDEEP
sqlarraysjsonpostgresqlaggregate-functions
Flow91
Flow91

Reputation: 31

PostgreSQL Json_build_object / json_agg add level of aggregation

I am using PostgreSQL 10.4, compiled by Visual C++ build 1800, 64-bit;

The query below yields a resultset of 1 column with type JSON and multiple rows, I have a requirement that the query returns every row into an array (basically 1 row and 1 column) with for example json_agg().

Unfortunately if I put json_agg() around the json_build_object I am getting an error that its impossible:

ERROR:  aggregate function calls cannot be nested
LINE 28:  '$values', json_agg(fv.*)

SELECT json_build_object(
    'id', vl.id,
    'id_form', vl.id_form,
    'id_waardenlijst', vl.id_waardenlijst,
    '$values', json_agg(fv.*)
) FROM var_list vl
    LEFT JOIN testscheme.form_values fv
    on fv.id_form_record = vl.id
    GROUP BY vl.id, vl.id_form, vl.id_waardenlijst

How can I add another layer of aggregation that returns me 1 column and 1 row with an array of my desired objects?

I hope I was clear!

Upvotes: 1

Views: 6746

Answers (1)

GMB
GMB

Reputation: 222682

Basically, you need another level of aggregation:

SELECT json_agg(js) js_final
FROM (
    SELECT json_build_object(
        'id', vl.id,
        'id_form', vl.id_form,
        'id_waardenlijst', vl.id_waardenlijst,
        '$values', json_agg(fv.*)
    ) js
    FROM var_list vl
    LEFT JOIN testscheme.form_values fv ON on fv.id_form_record = vl.id
    GROUP BY vl.id, vl.id_form, vl.id_waardenlijst
) t

Upvotes: 5

Related Questions