Question 90747 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL: Is it possible to count items in an array across rows?

My problem is that I have a table with data like this –

ID    COLORS
--------------------------------------------
1     ["red", "green"]
2     ["blue", "red"]
3     ["red", "green", "yellow", "blue"]

What SQL witchcraft will I need to wield in order to create a result like that? –

COLOR    COUNT
--------------------------------------------
red      3
blue     2
green    2
yellow   1

Is it even possible with either a SP or View?

Thank you!

Tags:

2

Answers


  1. 0 
    create table color_test (id integer, colors text[]);

insert into color_test values (1, ARRAY['red', 'green']), (2, ARRAY['blue', 'red']), (3, ARRAY['red', 'green', 'yellow', 'blue']);

select color, count(color) from color_test, unnest(colors) as color group by color;

 color  | count 
--------+-------
 red    |     3
 blue   |     2
 green  |     2
 yellow |     1
    Login or Signup to reply.
  2. 0

    You can flatten the arrays by using cross join and then apply count(*):

    select v.value#>>'{}', count(*) cnt 
from tbl t 
  cross join jsonb_array_elements(t.colors) v
group by v.value#>>'{}' 
order by cnt desc

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search