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

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!

CodePudding user response：

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

CodePudding user response：

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.