Re: array_to_json/array_agg question - Mailing list pgsql-general

From Laura Smith
Subject Re: array_to_json/array_agg question
Date
Msg-id JiF0sV__vWii0hEuyojbWLXfM98CyFL6cri9wJ0kwxg3LTPxWjCxHlmcC-sYsmBhpEvMF1I_u9GDIx9w-4q1vH481jDrORz2gSt0_Hp77dM=@protonmail.ch
Whole thread Raw
In response to Re: array_to_json/array_agg question  (Erik Wienhold <ewie@ewie.name>)
List pgsql-general

> 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
withthe 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,
soit would require an upgrade to 16 first, which is possible as a longer-term option, but right now I'm developing
againsta 14.5 backend). 



pgsql-general by date:

Previous
From: Zahir Lalani
Date:
Subject: RE: Mat Views and Conflicts
Next
From: Patrick FICHE
Date:
Subject: RE: array_to_json/array_agg question