title: How to aggregate rows of jsonb elements in postgres?
body: <p>I want to know if there is any efficient way to aggregate several rows of 
  a JSONB column, consider this table structure:</p>
  
  <pre><code>PERFORMANCE(user_id INTEGER, stat_date DATE, metrics JSONB)
  </code></pre>
  
  <p>With rows like this:</p>
  
  <pre><code>1, 2017-01-01, {"speed":10, "distance":120, "time":5}
  1, 2017-01-02, {"speed":15, "distance":150, "time":8}
  1, 2017-01-03, {"speed":9, "distance":90}
  2, 2017-01-01, {"speed":15, "distance":150, "time":8}
  </code></pre>
  
  <p>I would like to aggregate by SUM each key inside "metrics" column by user_id, so the output looks like this:</p>
  
  <pre><code>1, {"speed":34, "distance":360, "time":13}
  2, {"speed":15, "distance":150, "time":8}
  </code></pre>
  
  <p>One option is to use jsonb_each but that will explode the column and I was wondering if there is a better option.</p>
  
  <p>EDIT: It's important to note that I don't know every key inside the JSONB column, so I can't explicitly aggregate them.</p>
  
  <p>Thanks</p>