我使用postgresql 10,并假设我有下表:
CREATE TABLE test_table(id int8 NOT NULL, data jsonb);
并插入以下值:
insert into test_table(id, data)
values (1, '{"external": [{"internal_1": "value_1", "internal_2": "some_value"}]}'),
(2, '{"external": [{"internal_1": "value_2", "internal_2": "another_value"}]}'),
(3, '{"external": [{"internal_1": "value_1", "internal_2": "other_value"}]}');
毕竟这张table:
id| data |
--|-------------------------------------------------------------------------|
1 |{"external": [{"internal_1": "value_1", "internal_2": "some_value"}]} |
2 |{"external": [{"internal_1": "value_2", "internal_2": "another_value"}]} |
3 |{"external": [{"internal_1": "value_1", "internal_2": "other_value"}]} |
问题是我需要将所有内部值作为一个数组
我尝试了以下查询:
select data -> 'external'
from test_table;
结果是:
?column? |
-----------------------------------------------------------------|
[{"internal_1": "value_1", "internal_2": "some_value"}] |
[{"internal_1": "value_2", "internal_2": "another_value"}] |
[{"internal_1": "value_1", "internal_2": "other_value"}] |
但最后,我需要将内部值作为每个表行的文本数组。类似于:
?column? |
-------------------------------|
[value_1, some_value] |
[value_2, another_value] |
[value_1, other_value] |
是否可以使用postgresjson函数和操作符编写这样的查询?
升级!!内部密钥的数量可能不同(internal_1,…,internal_n),并且我们不预先知道密钥的名称(在我的示例中,它们是:internal_1和internal_2)
2条答案
按热度按时间s8vozzvw1#
一种方法是通过以显式的方式深入每个键,然后使用
jsonb_build_array()
功能:或更动态地(对于具有更多或更少内部键的情况)
jsonb_each_text()
可能在交叉联接查询中用于提取每个键,然后jsonb_agg()
可以应用于在数组中组合它们:演示
xnifntxz2#
以下查询应指向所需内容:
我们的想法是使用
json_build_array
函数来创建一个数组,您需要选择将成为其中一部分的每个元素,一种方便的方法是使用#>'{json_path}'
因此,您可以使用json路径选择元素。资料来源:https://www.postgresql.org/docs/10/functions-json.html
sqlfiddle来测试它:http://sqlfiddle.com/#!19年5月17日