Extract json array from postgres table gives error: cannot extract elements from a scalar
<p>By using <code>jsonb_array_elements()</code> function to extract out <code>jsonb</code> data array from Postgres, it gave error:</p>

<blockquote>
  <p>cannot extract elements from a scalar</p>
</blockquote>

<p>I assume that it is because of the <code>NULL</code> in the return call, added the <code>NULL</code> checking condition but not work. Any help appreciated.</p>

<pre><code>   select id ,
   CASE
    WHEN report IS NULL OR 
         (report-&gt;'stats_by_date') IS NULL OR 
         (report-&gt;'stats_by_date'-&gt; 'date') IS NULL then to_json(0)::jsonb
    ELSE jsonb_array_elements(report -&gt; 'stats_by_date' -&gt; 'date') 
    END AS Date
   from factor_reports_table
</code></pre>

<p>The truncated json array looks like: </p>

<blockquote>
  <p>"stats_by_date": {"date": [16632, 16633, 16634, ...], "imps": [2418, 896, 1005...], ...} </p>
</blockquote>