CODEWITHSUNDEEP
sqljsonpostgresql
David Sawatske
David Sawatske

Reputation: 111

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

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

Upvotes: 1

Views: 676

Answers (1)

D-Shih
D-Shih

Reputation: 46239

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

Upvotes: 2

Related Questions