title: Return elements of Redshift JSON array on separate rows
body: <p>I have a Redshift table that looks like this:</p>
  
  <pre><code> id | metadata
  ---------------------------------------------------------------------------
   1  | [{"pet":"dog"},{"country":"uk"}]
   2  | [{"pet":"cat"}]
   3  | []
   4  | [{"country":"germany"},{"education":"masters"},{"country":"belgium"}]
  </code></pre>
  
  <ul>
  <li>All array elements have just one field.</li>
  <li>There is no guarantee that a particular field will feature in any of an array's elements.</li>
  <li>A field name can be repeated in an array</li>
  <li>The array elements can be in any order</li>
  </ul>
  
  <p>I am wanting to get back a table that looks like this:</p>
  
  <pre><code> id |   field   |  value
  ------------------------
   1  | pet       | dog
   1  | country   | uk
   2  | pet       | cat
   4  | country   | germany
   4  | education | masters
   4  | country   | belgium
  </code></pre>
  
  <p>I can then combine this with my queries on the rest of the input table.</p>
  
  <p>I have tried playing around with the Redshift JSON functions, but without being able to write functions/use loops/have variables in Redshift, I really can't see a way to do this!</p>
  
  <p>Please let me know if I can clarify anything else.</p>