title: Nested JSON aggregation in Postgres
body: <p>I have a need to run a query over a Postgres database and aggregate it and export it as a json object using native Postgres tooling.</p>
  
  <p>I can't quite get the aggregation working correctly and I'm a bit stumped. </p>
  
  <p>Below is an example of some of the data </p>
  
  <p></p>
  
  <pre><code>| msgserial |  object_type  | payload_key |                          payload                          | user_id |
  +-----------+---------------+-------------+-----------------------------------------------------------+---------+
  |   1696962 | CampaignEmail | a8901b2c    | {"id": "ff7221da", "brand": "MAGIC", "eventType": "SENT"} |     001 |
  |   1696963 | OtherType     | b8901b2c    | {"id": "ff7221db", "brand": "MAGIC", "eventType": "SENT"} |     001 |
  |   1696964 | OtherType     | c8901b2c    | {"id": "ff7221dc", "brand": "MAGIC", "eventType": "SENT"} |     002 |
  |   1696965 | OtherType     | d8901b2c    | {"id": "ff7221dd", "brand": "MAGIC", "eventType": "SENT"} |     001 |
  |   1696966 | CampaignEmail | e8901b2c    | {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"} |     001 |
  |   1696967 | CampaignEmail | f8901b2c    | {"id": "ff7221df", "brand": "MAGIC", "eventType": "SENT"} |     002 |
  |   1696968 | SomethingElse | g8901b2c    | {"id": "ff7221dg", "brand": "MAGIC", "eventType": "SENT"} |     001 |
  +-----------+---------------+-------------+-----------------------------------------------------------+---------+
  </code></pre>
  
  <p>I need to output a JSON object like this grouped by user_id</p>
  
  <pre><code>{
    "user_id": 001,
    "brand": "MAGIC",
    "campaignEmails": [
      {"id": "ff7221da", "brand": "MAGIC", "eventType": "SENT"},
      {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"},
      {"id": "ff7221de", "brand": "MAGIC", "eventType": "SENT"}
    ],
    "OtherTypes": [
      {"id": "ff7221db", "brand": "MAGIC", "eventType": "SENT"},
      {"id": "ff7221dd", "brand": "MAGIC", "eventType": "SENT"}
    ],
    "Somethingelses": [
      {"id": "ff7221dg", "brand": "MAGIC", "eventType": "SENT"}
    ]
  },
  {
    "user_id": 002,
    "campaignEmails": [
    ],
    "OtherTypes": [
  
    ],
    "Somethingelses": [
    ]
  }
  </code></pre>
  
  <p>Essentially need to group al the payloads into arrays by their type grouped by the user_id </p>
  
  <p>I started with JSONB_BUILD_OBJECT getting one of the object_types grouped together into an array but then got stumped. </p>
  
  <p>Am I trying to achieve the impossible in raw PSQL? I'm really stumped and I keep hitting errors like X needs to be included in the GROUP BY clause etc...</p>
  
  <p>I can group one of the object_types into an array grouped by user_id but can't seem to do all 3 </p>
  
  <p>My other thinking was to do have 3 subqueries but I'm not sure how to do that either.</p>