title: Extract key, value from json objects in Postgres
body: <p>I have a Postgres table that has content similar to this:</p>
  
  <pre><code>id  | data
  
  1   | {"a":"4", "b":"5"}
  2   | {"a":"6", "b":"7"}
  3   | {"a":"8", "b":"9"}
  </code></pre>
  
  <p>The first column is an integer and the second is a json column.</p>
  
  <p>I want to be able to expand out the keys and values from the json so the result looks like this:</p>
  
  <pre><code>id  | key  | value
  
  1   | a    | 4
  1   | b    | 5
  2   | a    | 6
  2   | b    | 7
  3   | a    | 8
  3   | b    | 9
  </code></pre>
  
  <p>Can this be achieved in Postgres SQL?</p>
  
  <hr>
  
  <p><strong>What I've tried</strong></p>
  
  <p>Given that the original table can be simulated as such:</p>
  
  <pre><code>select *
  from 
  (
  values
  (1, '{"a":"4", "b":"5"}'::json),
  (2, '{"a":"6", "b":"7"}'::json),
  (3, '{"a":"8", "b":"9"}'::json)
  ) as q (id, data)
  </code></pre>
  
  <p>I can get just the keys using:</p>
  
  <pre><code>select id, json_object_keys(data::json)
  from 
  (
  values
  (1, '{"a":"4", "b":"5"}'::json),
  (2, '{"a":"6", "b":"7"}'::json),
  (3, '{"a":"8", "b":"9"}'::json)
  ) as q (id, data)
  </code></pre>
  
  <p>And I can get them as record sets like this:</p>
  
  <pre><code>select id, json_each(data::json)
  from 
  (
  values
  (1, '{"a":"4", "b":"5"}'::json),
  (2, '{"a":"6", "b":"7"}'::json),
  (3, '{"a":"8", "b":"9"}'::json)
  ) as q (id, data)
  </code></pre>
  
  <p>But I can't work out how to achieve the result with id, key and value.</p>
  
  <p>Any ideas?</p>
  
  <p>Note: the real json I'm working with is significantly more nested than this, but I think this example represents my underlying problem well.</p>