CountgreSQL array values

Question

I would like to count the elements of the array that correspond to true (attendance), false (not attendance) and NULL for any single event.

EDIT:

I only realized that arrays do not behave the way I thought in pSQL, so simple

userconfirm bool[] 

May be enough. However, I still have the same problem counting the values ​​true / false / null. I will try to edit the question below to fit this new restriction. We apologize for any errors.

---

I have a column like

 userconfirm bool[] 

Where userconfirm[314] = true means that user # 314. will be present (false = no attend, NULL = not read / etc).

I'm not sure if this is the best solution for this function (users announce their visit to the event), but I am having problems with the aggregate function in this column.

 select count(*) from foo where id = 6 AND true = ANY (userconfirm); 

This returns only 1, and an attempt to "count arrays" by Google will not add anything useful.

How do I calculate different values ​​for a single event?

Answer 1

You can use unnest in your SELECT, for example:

 select whatever, (select sum(case b when 't' then 1 else 0 end) from unnest(userconfirm) as dt(b)) from your_table -- ... 

For example, given this:

 => select * from bools; id | bits ----+-------------- 1 | {t,t,f} 2 | {t,f} 3 | {f,f} 4 | {t,t,t} 5 | {f,t,t,NULL} 

You will receive the following:

 => select id, (select sum(case b when 't' then 1 else 0 end) from unnest(bits) as dt(b)) as trues from bools; id | trues ----+------- 1 | 2 2 | 1 3 | 0 4 | 3 5 | 2 

If this is too ugly, you can write a function:

 create function count_trues_in(boolean[]) returns bigint as $$ select sum(case b when 't' then 1 else 0 end) from unnest($1) as dt(b) $$ language sql; 

and use it for your request:

 => select id, count_trues_in(bits) as trues from bools; id | trues ----+------- 1 | 2 2 | 1 3 | 0 4 | 3 5 | 2 

Answer 2

You can get the result of the array_length function:

 SELECT SUM(array_length(userconfirm, 2)) WHERE id = 6; 

Answer 3

This can do the trick ( disest ).

 postgres=# with y(res) as ( postgres(# with x(a) as ( postgres(# values (ARRAY[true,true,false]) postgres(# union all postgres(# values (ARRAY[true,null,false]) postgres(# ) postgres(# select unnest(a) as res postgres(# from x postgres(# ) postgres-# select count(*) postgres-# from y postgres-# where res; count ------- 3 (1 row) postgres=#