nested json aggregated rows on group by in postgresql

Question

I have a postgresql table

alok=# select * from people;
  name   | country |  city   
---------+---------+---------
 alok    | india   | delhi
 ram     | india   | kolkata
 shyam   | india   | kanpur
 krishna | us      | newyork
 ajit    | india   | delhi
 sami    | india   | delhi
 mohamad | india   | kolkata
(7 rows)

which can be produced by

create table people(name varchar, country varchar, city varchar);

insert into people values('alok', 'india', 'delhi');
insert into people values('ram', 'india', 'kolkata');
insert into people values('shyam', 'india', 'kanpur');
insert into people values('krishna', 'us', 'newyork');
insert into people values('ajit', 'india', 'delhi');
insert into people values('sami', 'india', 'delhi');
insert into people values('mohamad', 'india', 'kolkata');

I want to produce aggregated name on country and city like

country |                name                
---------+------------------------------------
 india   | {"delhi":["alok", "ajit", "sami"], "kolkata":["ram", "mohamad"], "kanpur":["shyam"]}
 us      | {"newyork":["krishna"]}

S-Man · Accepted Answer · 2019-07-02 07:44:33Z

3

demo:db<>fiddle

SELECT 
    country,
    json_object_agg(city, agg_names)
FROM (
    SELECT
        country,
        city, 
        json_agg(name) AS agg_names
    FROM
        people
    GROUP BY country, city
) s
GROUP BY country

Group the names by city. To put the names into one JSON array you can use the aggregate function json_agg()
Group this result by the country. To get the expected output you need to use the aggregate function json_object_agg() which creates key/pair values from city and name array

documentation

answered Jul 2, 2019 at 7:44

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alok · Accepted Answer · 2019-07-02 08:17:07Z

0

select 
   country, json_agg( json_build_object( 'city', city, 'name', name  ) ) 
from (
   select country, city, json_agg( name ) as name
   from people
   group by country, city 
) a 
group by a.country;

edited Jul 2, 2019 at 8:17

Alok

10.9k11 gold badges75 silver badges133 bronze badges

answered Jul 2, 2019 at 8:16

Shashank Shandilya

394 bronze badges

2 Comments

Andreas Over a year ago

Do not post just code. You should also add some explanation to your answer.

S-Man Over a year ago

Your code is wrong. You are creating an array finally. This is not what the TO expected. See difference: dbfiddle.uk/…

Collectives™ on Stack Overflow

nested json aggregated rows on group by in postgresql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related