从Postgres中的json对象中提取键和值 [英] Extract key, value from json objects in Postgres

问题描述

我有一个Postgres表，其内容类似于以下内容:

I have a Postgres table that has content similar to this:

id  | data

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

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

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:

id  | key  | value

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

这可以在Postgres SQL中实现吗?

Can this be achieved in Postgres SQL?

我尝试过的事情

鉴于原始表可以这样模拟:

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)

但是我不知道如何通过id，key和value来获得结果.

But I can't work out how to achieve the result with id, key and value.

有什么想法吗?

注意:我正在使用的真正的json比此嵌套的要多得多，但是我认为这个示例很好地说明了我的潜在问题.

Note: the real json I'm working with is significantly more nested than this, but I think this example represents my underlying problem well.

推荐答案

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的含义，这意味着对于表中的每一行，data列中的每个(key, value)对仅连接到该行，因此原始行与由对象得到维护.

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.

表q也可以是一个非常复杂的子查询(或VALUES子句，就像您的问题一样).在该函数中，从评估该子查询的结果中使用了适当的列，因此您仅使用对子查询的别名和子查询中(列的)列的引用.

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.

这篇关于从Postgres中的json对象中提取键和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！