PostgreSQL JSONB grouping array values inside a hash

Question

We have a PostgreSQL jsonb column containing hashes which in turn contain arrays of values:

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

What we'd like to do is create a jsonb query which would return

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

we've been trying something along the lines of

SELECT jsonb_to_recordset(hashes->>'sources')

but that's not working - any help with this hugely appreciated...

klin · Accepted Answer · 2016-06-13 13:47:28Z

1

The setup (should be a part of the question, note the proper json syntax):

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]}');

Use the function 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)

answered Jun 13, 2016 at 13:47

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Patrick · Accepted Answer · 2016-06-13 13:47:49Z

1

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

answered Jun 13, 2016 at 13:47

Patrick

33k7 gold badges73 silver badges102 bronze badges

Comments

user1051849 · Accepted Answer · 2016-06-13 14:25:30Z

0

We finally got this working this way:

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

but Klin's solution is more complete and both Klin and Patrick got there quicker than us (thank you both) - so points go to them.

answered Jun 13, 2016 at 14:25

user1051849

2,3375 gold badges27 silver badges44 bronze badges

Collectives™ on Stack Overflow

PostgreSQL JSONB grouping array values inside a hash

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related