title: How to convert JSON Array of Arrays to columns and rows
body: <p>I'm pulling data from an <a href="http://data.rio/dataset/gps-de-onibus/resource/015b775c-f303-4e50-9089-4b97b0674030" rel="nofollow">API</a> in JSON with a format like the example data below. Where essentially every "row" is an array of values. The API doc defines the columns and their types in advance. So I know the col1 is, for example, a varchar, and that col2 is an int.</p>
  
  <pre><code>CREATE TEMP TABLE dat (data json);
  INSERT INTO dat
  VALUES ('{"COLUMNS":["col1","col2"],"DATA":[["a","1"],["b","2"]]}');
  </code></pre>
  
  <p>I want to transform this within PostgreSQL 9.3 such that I end up with:</p>
  
  <pre><code>col1 | col2
  ------------
    a  |  1
    b  |  2
  </code></pre>
  
  <p>Using <a href="http://www.postgresql.org/docs/current/interactive/functions-json.html#FUNCTIONS-JSON-TABLE" rel="nofollow"><code>json_array_elements</code></a> I can get to:</p>
  
  <pre><code>SELECT json_array_elements(data-&gt;'DATA') 
  FROM dat
  
  json_array_elements
  json
  ---------
  ["a","1"]
  ["b","2"]
  </code></pre>
  
  <p>but then I can't figure out how to do either convert the JSON array to a PostgreSQL array so I can perform something like <code>unnest(ARRAY['a','1'])</code></p>