title: How do I calculate percent of whole within a Postgres JSON array?
body: <p>I've got several million rows in a PostgreSQL 9.3 table, and I'm struggling to calculate aggregate statistics.</p>
  
  <p>Here are the columns:</p>
  
  <p><code>object_id | date | column_with_json_data_in_key-value_format | bunch_of_irrelevant_columns</code></p>
  
  <p>There is one row for every object_id/date combo. There are approximately 70 keys in the JSON column, but any given row only contains ~5-20 keys.</p>
  
  <p><strong>Across all object_id's, for each key, what is the daily average fractional value?</strong></p>
  
  <ol>
  <li>Within each row, calculate the value of a particular key divided by the sum of all values for all keys for that particular row</li>
  <li>If a key doesn't appear in a row, treat it as having a value of 0 for that row</li>
  <li>Group the results by date</li>
  <li>Calculate the average value for each key</li>
  </ol>
  
  <p>So my desired output is a table with the following columns:</p>
  
  <p><code>date | key1_name | key1_fractional_average | keyN_name | keyN_fractional_average</code></p>
  
  <p>I tried this query for a single row, but it's not working correctly:</p>
  
  <pre><code>SELECT value2, total, (value2/total * 100) AS percent FROM
      (SELECT SUM(value) AS total FROM
          (SELECT CAST(CAST(value AS text) AS integer) FROM json_each((SELECT json_column
              FROM original_table
              WHERE primary_key = 1)::json)) as table1) as total_table,
      (SELECT CAST(CAST(value AS text) AS integer) AS value2 FROM json_each((SELECT json_column
              FROM original_table
              WHERE primary_key = 1)::json)) as value_table
  </code></pre>
  
  <p>For this particular page_id/insight_date row, the query returns the following:</p>
  
  <pre><code>value2 | total | percent
  ------------------------
  1;          4;         0
  2;          4;         0
  1;          4;         0
  </code></pre>
  
  <p>The columns <code>value2</code> and <code>total</code> are correct, but the <code>percent</code> column incorrectly shows 0's instead of the correct 25%, 50%, 25%.</p>
  
  <p>Unfortunately, because JSON is a fairly new type in Postgres, googling hasn't turned up many examples. I'm thoroughly confused here.</p>