title: Aggregating subquery keys to build a complex object
body: <p>I have a table that describes a list of products made for a given order, in this every row on this table has to have a an id of the product and the reason why it was purchased,</p>
  
  <p>I would like to build a json response that amounts to an array of locations the ordered product is for, within that, an array of <em>unique</em> product codes and with that an array of reasons <em>why</em> that unique product was ordered.</p>
  
  <p>I've only been able to get the topmost part of my query defined but the join and sub-select nature of the request is actually getting me in a bit of a fiddle. Is this kind of thing actually possible in plpgsql?</p>
  
  <p>Additionally I'd like to join product_order.reason on product_order_reason.id and retrieve the longform_text inside the table associated with this row but I figure the bigger thing is to get the return at all and that's where i've been left stumped.</p>
  
  <pre><code>product
  id      |name                    |cost  |cost_rate|
  --------|------------------------|------|---------|
  WALLC   |Wall Clock              | 15.00|SINGLE   |
  MIRR    |Mirror                  | 25.00|SINGLE   |
  KEY     |Door Keys               |  5.00|SINGLE   |
  KEYFOB  |Key Fob                 | 40.00|SINGLE   |
  
  
  
  product_order
  product_id|quantity|location  |quote_detail_quote_id               |is_primary_order|reason|
  ----------|--------|----------|------------------------------------|----------------|------|
  MIRR      |       2|floor_0   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     1|
  KEYF      |       3|floor_0   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     2|
  WALLC     |       3|floor_1   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     1|
  WALLC     |       3|floor_1   |C7D33FED-CB15-5796-DC7D-A7BCEA8923C5|true            |     3|
  product_order_reason
  ------------------------------------------------
  id (varchar, pk) | shortform_text(varchar) | longform_text(varchar)
  ------------------------------------------------
  
  
  id|shortform_text                       |longform_text                                        |
  --|-------------------------------------|-----------------------------------------------------|
   1|Employee Room                        |Standard employee room with no window                |
   2|Meeting Room                         |Standard Meeting Room                            |
   3|Mirror                               |Additional Mirror Request         |
  
  
  </code></pre>
  
  <pre><code>create
  or replace
  function get_breakdown_v1_0_0(p_quote_id character varying,
  p_location character varying,
  p_product_code character varying) returns json language plpgsql as $function$ declare row_count smallint := 0;
  begin
  raise notice 'Location: %',
  p_location;
  
  raise notice 'Product: %',
  p_product_code;
  -- Perform santiy check on quote_id so that the json does not include a null result.
   select
      count(*) into
          strict row_count
      from
          quote_detail
      where
          quote_id = p_quote_id;
  
  if row_count = 0 then raise 'Quote ID % not found',
  p_quote_id
      using ERRCODE = '02000';
  -- SQL standard no_data
   elseif row_count &gt; 1 then raise 'Too many rows returned for ID %',
  p_quote_id
      using ERRCODE = 'P0003';
  -- PL/pgSQL too_many_rows
  end if;
  -- Returns an object comprised of unique values for locations, where not null and their associated products
   return (
  select
      jsonb_build_object ('locations',jsonb_agg( jsonb_build_object( 'area', location, 'items', items)))
  from
      (
      select
          location,
          jsonb_agg(jsonb_build_object ('code', product_id, 'reasons', reason)) as items 
      from
          product_order
      where
          (quote_detail_quote_id = p_quote_id)
          and (location = p_location
          or p_location is null)
          and (product_id = p_product_code
          or p_product_code is null)
      group by
          location) a );
  end $function$ ;
  </code></pre>
  
  <p>Desired response;</p>
  
  <pre><code>        {
              "area": "floor_0",
              "items": [
                  {
                      "code": "WALLC",
                      "reasons": [
                          {
                              "quantity": 2,
                              "reason_code": "Standard Employee Room"
                          },
                          {
                              "quantity": 2,
                              "reason_code": "Standard Cubicle"
                          }
                      ]
                  },
                  {
                      "code": "MIRR",
                      "reasons": [
                          {
                              "quantity": 3,
                              "reason_code": "Meeting Room"
                          }
                      ]
                  }
              ]
          }]
  </code></pre>