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!
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
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
