I have a Postgres table that has content similar to this:
我有一个Postgres表,其内容与此类似:
id | data
1 | {"a":"4", "b":"5"}
2 | {"a":"6", "b":"7"}
3 | {"a":"8", "b":"9"}
The first column is an integer and the second is a json column.
第一列是整数,第二列是json列。
I want to be able to expand out the keys and values from the json so the result looks like this:
我希望能够扩展json中的键和值,因此结果如下所示:
id | key | value
1 | a | 4
1 | b | 5
2 | a | 6
2 | b | 7
3 | a | 8
3 | b | 9
Can this be achieved in Postgres SQL?
这可以在Postgres SQL中实现吗?
What I've tried
我试过的
Given that the original table can be simulated as such:
鉴于原始表可以这样模拟:
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)
I can get just the keys using:
我可以使用以下方法获取密钥:
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)
And I can get them as record sets like this:
我可以把它们作为这样的记录集:
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)
But I can't work out how to achieve the result with id, key and value.
但我无法弄清楚如何用id,key和value来实现结果。
Any ideas?
Note: the real json I'm working with is significantly more nested than this, but I think this example represents my underlying problem well.
注意:我正在使用的真正的json比这更嵌套,但我认为这个例子很好地代表了我的潜在问题。
1 个解决方案
#1
10
SELECT q.id, d.key, d.value
FROM q
JOIN json_each_text(q.data) d ON true
ORDER BY 1, 2;
The function json_each_text() is a set returning function so you should use it as a row source. The output of the function is here joined laterally to the table q, meaning that for each row in the table, each (key, value) pair from the data column is joined only to that row so the relationship between the original row and the rows formed from the json object is maintained.
函数json_each_text()是一个集合返回函数,因此您应该将其用作行源。函数的输出在这里横向连接到表q,这意味着对于表中的每一行,数据列中的每个(键,值)对仅连接到该行,因此原始行和行之间的关系由json对象形成的维护。
The table q can also be a very complicated sub-query (or a VALUES clause, like in your question). In the function, the appropriate column is used from the result of evaluating that sub-query, so you use only a reference to the alias of the sub-query and the (alias of the) column in the sub-query.
表q也可以是一个非常复杂的子查询(或VALUES子句,就像你的问题一样)。在函数中,从评估该子查询的结果中使用适当的列,因此您仅使用对子查询的别名和子查询中的(别名)列的引用。
#1
10
SELECT q.id, d.key, d.value
FROM q
JOIN json_each_text(q.data) d ON true
ORDER BY 1, 2;
The function json_each_text() is a set returning function so you should use it as a row source. The output of the function is here joined laterally to the table q, meaning that for each row in the table, each (key, value) pair from the data column is joined only to that row so the relationship between the original row and the rows formed from the json object is maintained.
函数json_each_text()是一个集合返回函数,因此您应该将其用作行源。函数的输出在这里横向连接到表q,这意味着对于表中的每一行,数据列中的每个(键,值)对仅连接到该行,因此原始行和行之间的关系由json对象形成的维护。
The table q can also be a very complicated sub-query (or a VALUES clause, like in your question). In the function, the appropriate column is used from the result of evaluating that sub-query, so you use only a reference to the alias of the sub-query and the (alias of the) column in the sub-query.
表q也可以是一个非常复杂的子查询(或VALUES子句,就像你的问题一样)。在函数中,从评估该子查询的结果中使用适当的列,因此您仅使用对子查询的别名和子查询中的(别名)列的引用。