How to create nested JSON return with aggregate function and dynamic key values using `jsonb_build_object`

Question

This is what and example of the table looks like.

+---------------------+------------------+------------------+
|    country_code     |      region      |   num_launches   |
+---------------------+------------------+------------------+
|        'CA'         |     'Ontario'    |         5        |
+---------------------+------------------+------------------+
|        'CA'         |     'Quebec'     |         9        |
+---------------------+------------------+------------------+
|        'DE'         |     'Bavaria'    |         15       |
+---------------------+------------------+------------------+
|        'DE'         |    'Saarland'    |         12       |
+---------------------+------------------+------------------+
|        'DE'         |     'Berlin'     |         23       |
+---------------------+------------------+------------------+
|        'JP'         |     'Tokyo'      |         19       |
+---------------------+------------------+------------------+

I am able to write a query that returns each country_code with all regions nested within, but I am unable to get exactly what I am looking for.

My intended return looks like.

[
  { 'CA': [
      { 'Ontario': 5 },
      { 'Quebec': 9 }
    ]
  },
  { 'DE': [
      { 'Bavaria': 15 },
      { 'Saarland': 12 },
      { 'Berlin': 23 }
    ]
  },
  { 'JP': [
      { 'Tokyo': 19 }
    ]
  }
]

How could this be calculated if the num_launches was not available?

+---------------------+------------------+
|    country_code     |      region      |
+---------------------+------------------+
|        'CA'         |     'Ontario'    |
+---------------------+------------------+
|        'CA'         |     'Ontario'    |
+---------------------+------------------+
|        'CA'         |     'Ontario'    |
+---------------------+------------------+
|        'CA'         |     'Quebec'     |
+---------------------+------------------+
|        'CA'         |     'Quebec'     |
+---------------------+------------------+
|        'DE'         |     'Bavaria'    |
+---------------------+------------------+
|        'DE'         |     'Bavaria'    |
+---------------------+------------------+
|        'DE'         |     'Bavaria'    |
+---------------------+------------------+
|        'DE'         |     'Bavaria'    |
+---------------------+------------------+
|        'DE'         |    'Saarland'    |
+---------------------+------------------+
|        'DE'         |     'Berlin'     |
+---------------------+------------------+
|        'DE'         |     'Berlin'     |
+---------------------+------------------+
|        'JP'         |     'Tokyo'      |
+---------------------+------------------+

Expected Return

[
  { 'CA': [
      { 'Ontario': 3 },
      { 'Quebec': 2 }
    ]
  },
  { 'DE': [
      { 'Bavaria': 4 },
      { 'Saarland': 1 },
      { 'Berlin': 2 }
    ]
  },
  { 'JP': [
      { 'Tokyo': 1 }
    ]
  }
]

Thanks

D-Shih · Accepted Answer · 2018-10-24 20:53:49Z

2

You can try to use json_agg with json_build_object function in a subquery to get the array then do it again in the main query.

Schema (PostgreSQL v9.6)

CREATE TABLE T(
   country_code varchar(50),
   region  varchar(50),
  num_launches int
);


insert into t values ('CA','Ontario',5);      
insert into t values ('CA','Quebec',9);       
insert into t values ('DE','Bavaria',15);     
insert into t values ('DE','Saarland',12);    
insert into t values ('DE','Berlin',23);      
insert into t values ('JP','Tokyo',19);

Query #1

select json_agg(json_build_object(country_code,arr)) results
from (
  SELECT country_code,
         json_agg(json_build_object(region,num_launches)) arr 
  FROM T
  group by country_code
) t1;

results

[{"CA":[{"Ontario":5},{"Quebec":9}]},{"DE":[{"Bavaria":15},{"Saarland":12},{"Berlin":23}]},{"JP":[{"Tokyo":19}]}]

View on DB Fiddle

edited Oct 24, 2018 at 20:53

answered Oct 24, 2018 at 20:48

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

1 Comment

David Sawatske Over a year ago

How would I adjust the query if the num_launches column were not immediately available. I put an example in the question body.

Collectives™ on Stack Overflow

How to create nested JSON return with aggregate function and dynamic key values using `jsonb_build_object`

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related