title: How to parse a table with a JSON array field in PostgreSQL into rows?
body: <p><br>
  I have a table that contains a json array. Here is a sample of the contents of the field from:</p>
  
  <pre><code>SELECT json_array FROM table LIMIT 5;
  </code></pre>
  
  <p>Result:</p>
  
  <blockquote>
    <p>[{"key1":"value1"}, {"key1":"value2"}, ..., {"key2":"value3"}]<br>
    []<br>
    []<br>
    []{"key1":"value1"}<br>
    []</p>
  </blockquote>
  
  <p>How can I retrieve all the values and count how many of each value was found?</p>
  
  <p>I am using PostgreSQL 9.5.14, and I have tried the solutions here <a href="https://stackoverflow.com/questions/51255641/querying-a-json-array-of-objects-in-postgres">Querying a JSON array of objects in Postgres</a>
  and the ones suggested to me by another generous stackoverflow user in my last question: <a href="https://stackoverflow.com/questions/56217300/how-can-i-parse-json-arrays-in-postgresql">How can I parse JSON arrays in postgresql?</a></p>
  
  <p>I tried: <br></p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT 
      value -&gt; 'key1'
  FROM
      table,
      json_array_elements(json_array);
  </code></pre>
  
  <p>which sadly does not work for me due to receiving the error:<br> <em>cannot call json_array_elements on a scalar</em><br>
  <a href="https://www.postgresql.org/docs/9.1/sql-expressions.html" rel="nofollow noreferrer">This error happens when using a query that returns more than one row or more than one column as a scalar subquery.</a></p>
  
  <p>Another solution I tried was:<br></p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT json_array as json, (json_array-&gt;0), 
    coalesce(
      case
        when (json_array-&gt;0) IS NULL then null
        else (json_array-&gt;0-&gt;&gt;'key1')
      end,
    'No value') AS "Value"
  FROM table;
  </code></pre>
  
  <p>which only returned null values for the "Value"<br></p>
  
  <p>Referencing <a href="https://stackoverflow.com/questions/51255641/querying-a-json-array-of-objects-in-postgres">Querying a JSON array of objects in Postgres</a> I attempted to use this solution as well:<br></p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH json_test (col) AS (
  values (json_arrays)
  )
  SELECT
    y.x-&gt;'key1' "key1"
  FROM json_test jt, 
  LATERAL (SELECT json_array_elements(jt.col) x) y;
  </code></pre>
  
  <p>But I would need to be able to fit all the elements of the json_arrays into json_test</p>
  
  <p>So far I have only attempted to list all the values in the all json arrays, but my ideal end-result for the query resembles this:<br></p>
  
  <p>Value   | Amount<br>
  ---------------<br>
  value1  |  48<br>
  value2  |  112<br>
  value3  |  93<br>
  value4  |  0<br></p>
  
  <p>Yet again I am grateful for any help with this, thank you in advance.</p>