我有一个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,data)
我可以把它们作为这样的记录集：
select id,json_each(data::json) from ( values (1,data)
但我无法弄清楚如何用id,key和value来实现结果.
有任何想法吗？
注意：我正在使用的真正的json比这更嵌套,但我认为这个例子很好地代表了我的潜在问题.
解决方法
SELECT q.id,d.key,d.value FROM q JOIN json_each_text(q.data) d ON true ORDER BY 1,2;
函数json_each_text()是一个集合返回函数,因此您应该将其用作行源.函数的输出在这里是表q的joined laterally,这意味着对于表中的每一行,数据列中的每个(键,值)对仅连接到该行,因此原始行与形成的行之间的关系从json对象维护.
表q也可以是一个非常复杂的子查询(或VALUES子句,就像你的问题一样).在函数中,从评估该子查询的结果中使用适当的列,因此您仅使用对子查询的别名和子查询中的(别名)列的引用.
