title: Json column with array to record
body: <p>I need to write SQL code, that INSERT into some table data, that stored in another table as JSON. PostgreSQL 9.5.</p>
  
  <p>I have a table named <strong>comments</strong>. It has a JSON colum <strong>refs</strong> with data like this:</p>
  
  <pre class="lang-json prettyprint-override"><code>[{"author":"John","tags":["ruby","rails"]}, {"author":"Nick","tags":["sql"]}]
  </code></pre>
  
  <p>As you can see, there maybe more than one item (pair) in the JSON.</p>
  
  <p>I need to write SQL code, that will take all the records from <em>comments</em> where <em>refs</em> IS NOT NULL and INSERT INTO <strong>comments_refs</strong> (dont ask why am I need it :) ), that looks like:</p>
  
  <pre><code>id                | integer                | not null default nextval(...)
  comment_id        | integer                | not null
  author            | character varying(255) | not null
  tags              | text[]                 | not null default '{}'::text[]
  </code></pre>
  
  <p>I try to play with <code>json_to_recordset</code>, but it doesn't work with arrays (see <a href="http://postgresql.nabble.com/bug-in-json-to-record-with-arrays-td5828415.html" rel="nofollow">http://postgresql.nabble.com/bug-in-json-to-record-with-arrays-td5828415.html</a> ). Next, I try something like:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT json_array_elements(rec.refs) FROM comments AS rec;
  </code></pre>
  
  <p>but I do not come up with how to do it .. Maybe somebody can help me. Thanks.</p>