哈希PostgreSQL内部JSONB分组数组值

Question

我们有一个包含哈希又包含值的数组一个PostgreSQL jsonb柱：

id | hashes 
--------------- 
1 | {"sources"=>["a","b","c"], "ids"=>[1,2,3]} 
2 | {"sources"=>["b","c","d","e","e"], "ids"=>[1,2,3]} 

我们想要做的是创造一个jsonb查询这将返回

code | count 
--------------- 
"a" | 1 
"b" | 2 
"c" | 2 
"d" | 1 
"e" | 2 

我们一直在试图沿着

SELECT jsonb_to_recordset(hashes->>'sources') 

东西线0

但是这不工作 - 与此非常感激任何帮助......

Answer 1

的设置（应该是问题的一部分，注意正确的JSON语法）：

create table a_table (id int, hashes jsonb); 
insert into a_table values 
(1, '{"sources":["a","b","c"], "ids":[1,2,3]}'), 
(2, '{"sources":["b","c","d","e","e"], "ids":[1,2,3]}'); 

使用功能jsonb_array_elements()：

select code, count(code) 
from 
    a_table, 
    jsonb_array_elements(hashes->'sources') sources(code) 
group by 1 
order by 1; 

code | count 
------+------- 
"a" |  1 
"b" |  2 
"c" |  2 
"d" |  1 
"e" |  2 
(5 rows)  

Answer 2

SELECT h, count(*) 
FROM (
    SELECT jsonb_array_elements_text(hashes->'sources') AS h FROM mytable 
) sub 
GROUP BY h 
ORDER BY h; 

Answer 3

我们终于得到了这方面的工作是这样的：

SELECT jsonb_array_elements_text(hashes->'sources') as s1, 
count(jsonb_array_elements_text(hashes->'sources')) 
FROM a_table 
GROUP BY s1; 

但是Klin的解决方案更加完整，Klin和Patrick都比我们更快（谢谢你们） - 所以积分会转到他们身上。