Using `jsonb_build_object` to return object with dynamic keys and aggregate values

Question

Starting with the data table below, how can I get a return JSON object with the keys for each region with the sum of their occurrence as the value, nested within the region's country_code?

Example Table

+---------------------+------------------+
|    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 Result

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

cske · Accepted Answer · 2018-10-25 17:59:20Z

Quick and dirty

with tbl(country_code,region) 
  as (values ('CA', 'Ontario')
  ,('CA', 'Ontario')
  ,('CA', 'Ontario')
  ,('CA', 'Quebec')
  ,('CA', 'Quebec')
  ,('DE', 'Bavaria')
  ,('DE', 'Bavaria')
  ,('DE', 'Bavaria')
  ,('DE', 'Saarland')
  ,('DE', 'Berlin')
  ,('DE', 'Berlin')
  ,('JP', 'Tokyo')
)
, groups as (select country_code,jsonb_build_object(region,count(*)) as x from tbl group by country_code,region)
, l1 as (select jsonb_build_object(country_code,jsonb_agg(x)) as y from groups group by country_code)
select jsonb_agg(y) from l1;

Returns:

jsonb_agg                                                                                                                   |
----------------------------------------------------------------------------------------------------------------------------|
[{"CA": [{"Quebec": 2}, {"Ontario": 3}]}, {"JP": [{"Tokyo": 1}]}, {"DE": [{"Saarland": 1}, {"Berlin": 2}, {"Bavaria": 3}]}] |

Collectives™ on Stack Overflow

Using `jsonb_build_object` to return object with dynamic keys and aggregate values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related