如何提取JSONB数组的元素？

Question

运行PostgresSQL v10.5。

在我的表table_a中，有一个列metadata，类型为jsonb。

它有一个JSON数组作为其中的一个键array_key，其值类似于：

[{"key1":"value11", "key2":"value21", "key3":"value31"}, 
 {"key1":"value21", "key2":"value22", "key3":"value23"}]

这是我如何查询这个关键字的方法

SELECT metadata->>'array_key' from table_a

这给了我整个数组。有没有办法只查询选定的键并格式化它们？

数组的类型是文本，即pg_typeof(metadata->>'array_key')为text

理想的输出应该是：

"value11, value13", "value21, value23"

- axay

Answer 1

使用jsonb_array_elements()函数获取数组的元素（作为value），可以通过键进行过滤：

select value->>'key1' as key1, value->>'key3' as key3
from table_a
cross join jsonb_array_elements(metadata->'array_key');

  key1   |  key3   
---------+---------
 value11 | value31
 value21 | value23
(2 rows)

使用聚合函数来将每行的输出作为单个值，例如：

select string_agg(concat_ws(', ', value->>'key1', value->>'key3'), '; ')
from table_a
cross join jsonb_array_elements(metadata->'array_key')
group by id;

             string_agg             
------------------------------------
 value11, value31; value21, value23
(1 row)

rextester上的工作示例。

- klin