我有一个 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 比这嵌套得多,但我认为这个例子很好地代表了我的潜在问题。