从 Postgres 中的 json 对象中提取键、值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39010601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract key, value from json objects in Postgres
提问by Tom G
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 比这更嵌套，但我认为这个例子很好地代表了我的潜在问题。
回答by Patrick
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 laterallyto the table q, meaning that for each row in the table, each (key, value)pair from the datacolumn is joined only to that row so the relationship between the original row and the rows formed from the jsonobject is maintained.
该函数json_each_text()是一个集合返回函数，因此您应该将其用作行源。函数的输出在这里横向连接到 table q，这意味着对于table中的每一行，(key, value)来自data列的每一对仅连接到该行，因此原始行和由json对象形成的行之间的关系得以保持。
The table qcan also be a very complicated sub-query (or a VALUESclause, 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子句，就像你的问题一样）。在该函数中，从评估该子查询的结果中使用适当的列，因此您仅使用对子查询别名和子查询中（的别名）列的引用。
回答by Gregorio Freidin
Another way that i think is very easy to work when you have multiple jsons to join is doing something like:
当您有多个 jsons 要加入时，我认为很容易使用的另一种方法是执行以下操作：
SELECT data -> 'key' AS key,
data -> 'value' AS value
FROM (SELECT Hstore(Json_each_text(data)) AS data
FROM "your_table") t;
