count distinct records from jsonb column

Question

I have a column stored as jsonb which is an array of number. Values look like this

I'd like to count the total distinct values from this column. I can get the distinct values like below

SELECT DISTINCT JSONB_ARRAY_ELEMENTS_TEXT(aimjoined) AS aims FROM mytable;

I can't figure out how to count the total number of occurences for each of the unique values.

Answer 1

First flatten the table using a lateral join and then do a trivial group by.

SELECT aims::numeric, count(*)
FROM mytable, 
lateral jsonb_array_elements(aimjoined) AS aims
group by aims;