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

qlzsbp2j  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(376)

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

s8vozzvw

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

演示

xnifntxz

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日

相关问题