In PostgreSQL, how can I convert some boolean columns into an array of those, that are true?

Given this table

is_a is_b is_c
t    t    f
f    f    t
f    t    t
...

How can I select it into this result?

{'a','b'}
{'a','c'}
{'b','c'}

Answer

You can convert the whole row into a JSON, then use a JSON Path query to get only those keys (=columns) that are true:

select jsonb_path_query_array(to_jsonb(t), '$.keyvalue() ? (@.value == true).key')
from the_table t

to_jsonb(t) converts the whole row into a JSON value. The JSON Path expression splits that into key/value pairs and then aggregates the keys of the “true” values into an array.

Online example

---

If you want a native Postgres array, rather than a JSON array, you can do something similar:

select t.*, 
       (select array_agg(key) 
       from jsonb_each(to_jsonb(t)) as x(key, value)
       where value = 'true')
from the_table t;

This again turns the row into a JSON, then generates one row per key and aggregates that back into a native Postgres array.

Online example