From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | array_to_json/array_agg question |
Date: | 2024-02-20 09:48:29 |
Message-ID: | XU4mUtNxxHsKEvDTzORdB7C656fZMrqdxGecPftMmlecmwweDf4bOcGVERKMwgka4_Ce8dCHlx_ifqX7rvT9UgGHJfu1n6l5h1Gpn3IADe8=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
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"}]
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2024-02-20 11:32:23 | Re: array_to_json/array_agg question |
Previous Message | Laurenz Albe | 2024-02-20 08:42:51 | Re: Identifying optimizer usage of indexed expressions |