title: Flatten aggregated key/value pairs from a JSONB field?
body: <p>I am working in Postgres 9.4 with the following table:</p>
  <pre class="lang-none prettyprint-override"><code>     Column      │         Type         │ Modifiers
  ─────────────────┼──────────────────────┼──────────────────────
   id              │ integer              │ not null default
   practice_id     │ character varying(6) │ not null
   date            │ date                 │ not null
   pct_id          │ character varying(3) │
   total_list_size │ double precision     │ not null
   star_pu         │ jsonb                │
  </code></pre>
  <p>I have the following query:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT date,
         AVG(total_list_size) AS total_list_size,
         json_object_agg(key, val) AS star_pu
  FROM (SELECT date,
               SUM(total_list_size) AS total_list_size,
               key, SUM(value::numeric) val FROM frontend_practicelist p,
               jsonb_each_text(star_pu)
         GROUP BY date, key ) p
  GROUP BY date
  ORDER BY date;
  </code></pre>
  <p>It gives me results with a JSON object attached to <code>star_pu</code>:</p>
  <pre class="lang-none prettyprint-override"><code>date            │ 2013-04-01
  total_list_size │ 56025123.000000000000
  star_pu         │ { &quot;antidepressants_cost&quot; : 180102416.8036909901975399, &quot;antiepileptic_drugs_cost&quot; : 296228344.171576079922216... }
  </code></pre>
  <p>Instead I would like to flatten the JSON result to a series of namespaced keys, so the result looks like this:</p>
  <pre class="lang-none prettyprint-override"><code>date                             │ 2013-04-01
  total_list_size                  │ 56025123.000000000000
  star_pu.antidepressants_cost     │ 180102416.8036909901975399
  star_pu.antiepileptic_drugs_cost │ 296228344.171576079922216 
  ...
  </code></pre>
  <p>Is this possible?</p>