sql to pull values from array

I have values in array like below

Column X
[ "A", "B", "C", "D", "E" ]
[ "A", "B"]

trying to get a result set like below,

Value  count
A  2
B  2
C  1
D  1
E  1

Answer

I think you can use flatten function :

select value , count(*)
from yourtable a
cross join table(flatten(a.ColumnX)) f
group by f.value
order by count(*)