calculate sum of two columns in PostgreSQL 9.5
<p>I have a table of posts, each with an insight jsonb column containing something like the data sample below.</p>

<p><strong>Data sample (comes from Facebook, so cannot change format)</strong></p>

<pre><code>[
  {
    "name": "post_story_adds_unique",
    "values": [
      { "value": 93 }
    ],
  },
  {
    "name": "post_story_adds",
    "values": [
      {  "value": 100 }
    ]
  },
  {
    "name": "post_impressions_organic_unique",
    "values": [
      {  "value": 123 }
    ]
  },
  ...
]
</code></pre>

<p>I want to have a calculated sum of <strong>reach</strong> and <strong>viral</strong> and then have it ordered by the total.</p>

<p><strong>Desired Results</strong></p>

<pre><code>id  message                       post_created          reach   viral   total
69  This World Family dablah...   2016-05-11 18:44:16   6683    646     7329
...
</code></pre>

<p>I managed to get the results so far but I cannot figure how to get the sum of the two columns. I don't know where to add another join or select to sum the two columns.</p>

<p><strong>Results so far</strong></p>

<pre><code>id  message                       post_created          reach   viral
69  This World Family dablah...   2016-05-11 18:44:16   6683    646
58  blah blah flip flop blah...   2016-05-22 11:00:01   4880    403
55  This is another message ...   2016-05-24 10:00:00   4417    109
</code></pre>

<p>I've tried various ways such as including the <code>SUM (reach + viral) as total</code> in the first <code>SELECT</code> but mostly I get back errors saying columns don't exist.</p>

<p><strong>Heres my SQL so far:</strong></p>

<pre><code>SELECT
    id,
    message,
    post_created,
    obj.value-&gt;'values'-&gt;0-&gt;'value' AS reach,
    obj2.value-&gt;'values'-&gt;0-&gt;'value' AS viral
  FROM (
    SELECT
      id,
      message,
      post_created,
      insights
    FROM posts
    WHERE (
      page_id = 4 AND
      post_created &gt;= '2016-05-01 00:00:00' AND
      post_created &lt;= '2016-05-31 23:59:59' AND
      insights @&gt; '[{"name":"post_impressions_organic_unique"}, {"name":"post_impressions_viral_unique"}]'
    )
  ) e1
  JOIN LATERAL jsonb_array_elements(insights) obj(value) ON obj.value-&gt;&gt;'name' = 'post_impressions_organic_unique'
  JOIN LATERAL jsonb_array_elements(insights) obj2(value) ON obj2.value-&gt;&gt;'name' = 'post_impressions_viral_unique'
  ORDER BY reach DESC;
</code></pre>