我有一个 Postgres 表，其内容类似于：id | data1 | {\'a\':\'4\', \'b\':\'5\'}2 | {\'a\':\'6\', \'b\':\'7\'}3 | {\'a\':\'8\', \'b\':\'9\'} 第一列是整数，第二列是......

我有一个 Postgres 表，其内容类似如下：

id  | data

1   | {"a":"4", "b":"5"}
2   | {"a":"6", "b":"7"}
3   | {"a":"8", "b":"9"}

第一列是整数，第二列是 json 列。

我希望能够从 json 中扩展键和值，以便结果如下所示：

id  | key  | value

1   | a    | 4
1   | b    | 5
2   | a    | 6
2   | b    | 7
3   | a    | 8
3   | b    | 9

这可以在 Postgres SQL 中实现吗？

我尝试过的方法

假设原始表可以这样模拟：

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)

我可以使用以下方法获取密钥：

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)

我可以将它们作为记录集获取，如下所示：

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)

但我不知道如何用id、key和value来实现结果。

有什么想法吗？

注意：我正在使用的实际 json 比这嵌套得多，但我认为这个例子很好地代表了我的潜在问题。