Extraer clave, valor de objetos json en Postgres
Tengo una tabla de Postgres que tiene contenido similar a este:
id | data
1 | {"a":"4", "b":"5"}
2 | {"a":"6", "b":"7"}
3 | {"a":"8", "b":"9"}
La primera columna es un número entero y la segunda es una columna json.
Quiero poder expandir las claves y los valores de json para que el resultado se vea así:
id | key | value
1 | a | 4
1 | b | 5
2 | a | 6
2 | b | 7
3 | a | 8
3 | b | 9
¿Se puede lograr esto en Postgres SQL?
Lo que he intentado
Dado que la tabla original se puede simular como tal:
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)
Puedo obtener solo las teclas usando:
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)
Y puedo obtenerlos como conjuntos de registros como este:
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)
Pero no puedo entender cómo lograr el resultado con id, key y value.
¿Algunas ideas?
Nota: el json real con el que estoy trabajando está significativamente más anidado que esto, pero creo que este ejemplo representa bien mi problema subyacente.