RE: array_to_json/array_agg question

On 2024-02-20 10:48 +0100, Laura Smith wrote:
> Before I go down the road of taking care of this in the front-end
> through iterations, I thought I would ask the pgsql if there was a
> clever query I could do on postgres that would take care of it for me
> instead.
>
> In essence, I would like to consolidate values from the same key as a json array, so instead of :
> [{"key":"one","value_1":"foo","value_2":"foo"},{"key":"one","value_1":
> "foo","value_2":"bar"}] I would have (forgive me if my JSON syntax is
> incorrect here) :
> [{"key":"one",[{"value_1":"foo","value_2":"foo"},{"value_1":"foo","val
> ue_2":"bar"}]}]
>
> A simplified example of where I am at the moment:
>
> create table test_a(key text,value_1 text,value_2 text);insert into
> test_a(key,value_1,value_2) values('one','foo','foo'); insert into
> test_a(key,value_1,value_2) values('one','foo','bar'); insert into
> test_a(key,value_1,value_2) values('two','bar','foo'); select
> array_to_json(array_agg(row_to_json(p))) from (select * from test_a
> where key='one') p;
> [{"key":"one","value_1":"foo","value_2":"foo"},{"key":"one","value_1":
> "foo","value_2":"bar"}]

May be something like this
SELECT jsonb_agg( value )
FROM ( SELECT jsonb_build_object( 'key', key, 'value', jsonb_agg( jsonb_build_object( 'value_1', value_1, 'value_2', value_2 ) ) ) FROM test_a GROUP BY key ) per_key( value )