> You almost got the subrecord ("value_1" and "value_2") right. You need
> to use json_build_object() (or even the new json_object() function added
> in pg16) instead of row_to_json() to just include "value_1" and
> "value_2". Then GROUP BY "key" and aggregate the subrecords with
> json_agg(). Then build the top-level record ("key" and "values") with
> json_build_object(). And finally one more aggregation with json_agg()
> to get a single array.
>
Interesting ideas, thanks Erik.
Subsequent to my original posting, but prior to your reply and based on an off-list idea from someone else, I came up with the following adaptation:
SELECT json_agg(q) INTO v_res FROM (SELECT array_to_json(array_agg(row_to_json(p))) AS q
FROM (SELECT * FROM test_a)p group by key)s;
But maybe I should be considering json_build_object() instead, or maybe json_object() (although I'm currently on 14.5, so it would require an upgrade to 16 first, which is possible as a longer-term option, but right now I'm developing against a 14.5 backend).
