Extract key, value from json objects in Postgres
<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>