sql—是否可以从postgres中的json数组中获取列表形式的json值？

qlzsbp2j 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(382)

我使用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）

sql postgresql JSON Jsonb

来源：https://stackoverflow.com/questions/62104735/is-it-possible-to-get-json-values-as-list-from-array-of-json-in-postgres

2条答案

按热度按时间

s8vozzvw1#

一种方法是通过以显式的方式深入每个键，然后使用 jsonb_build_array() 功能：

SELECT jsonb_build_array(
       (data ->> 'external')::jsonb -> 0 ->> 'internal_1',
       (data ->> 'external')::jsonb -> 0 ->> 'internal_2')
  FROM test_table

或更动态地（对于具有更多或更少内部键的情况） jsonb_each_text() 可能在交叉联接查询中用于提取每个键，然后 jsonb_agg() 可以应用于在数组中组合它们：

SELECT id, 
       jsonb_agg(js.value)
  FROM test_table
 CROSS JOIN jsonb_each_text( ((data ->> 'external')::jsonb -> 0)::jsonb) AS js
 GROUP BY id
 ORDER BY id

演示

赞(0）回复(0）举报 2021-08-13

xnifntxz2#

以下查询应指向所需内容：

select json_build_array(data#>>'{external,0, internal_1}', data#>>'{external,0, internal_2}')
from test_table;

我们的想法是使用 json_build_array 函数来创建一个数组，您需要选择将成为其中一部分的每个元素，一种方便的方法是使用 #>'{json_path}' 因此，您可以使用json路径选择元素。
资料来源：https://www.postgresql.org/docs/10/functions-json.html
sqlfiddle来测试它：http://sqlfiddle.com/#!19年5月17日

赞(0）回复(0）举报 2021-08-13

我来回答

sql—是否可以从postgres中的json数组中获取列表形式的json值？

2条答案

相关问题

热门标签

最新问答