Re: array_to_json/array_agg question
От | Erik Wienhold |
---|---|
Тема | Re: array_to_json/array_agg question |
Дата | |
Msg-id | xe7mbruj5uf4hnfch5ggy52cmtkjk4iegywm4hx7prd65hiec4@mg3t25synzez обсуждение исходный текст |
Ответ на | array_to_json/array_agg question (Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch>) |
Ответы |
Re: array_to_json/array_agg question
RE: array_to_json/array_agg question |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: