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","value_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"}]

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.

--
Erik

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

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 )