Build array from boolean columns

Question

I have columns like role1, role2, role3, etc. They are all booleans.

I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the view would contain ["role1", "role3"].

Is there any good way to do this that doesn't explode into a ton of CASE WHEN´s? To clarify, I'd be fine with O(n) with CASE WHEN, but not O(2^n) which is what it currently seems to need. :)

Answer 1

I suggest a VALUES expression in the lateral subquery.

SELECT t.id, a.roles
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT col
      FROM  (
         VALUES
            ('role1', role1) -- eligible columns
          , ('role2', role2)
          , ('role3', role3)
         ) x(col, val)
      WHERE  val
     )
  ) a(roles);

This "unpivots" columns to rows, so we can process a set instead of a row.

As a_horse demonstrates, json(b) is versatile enough to also cover this task. And you don't need to spell out eligible columns while all boolean columns are processed. But it seems you have to spell out eligible columns anyway.

Subtle difference: this returns an empty array for "no qualifying values" ({}), while a_horse's query returns NULL for the same.

This should be substantially faster for three reasons:

1. Only processes eligible columns to begin with. Especially relevant with many additional (possibly big?) columns.
2. Involves less casting back and forth and the predicate is as cheap as it gets.
3. ARRAY constructor is faster than array_agg(). See:
   • Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Or, simpler yet: plain CASE expressions concatenated with concat_ws(). If a string is good enough:

SELECT t.id
     , concat_ws(','
               , CASE WHEN role1 THEN 'role1' END -- eligible columns
               , CASE WHEN role2 THEN 'role2' END
               , CASE WHEN role3 THEN 'role3' END) AS roles_string
FROM   tbl t;

Should be the fastest possible solution. And not that much more verbose. See:

• How to concatenate columns in a Postgres SELECT?
• Combine two columns and add into one new column

Or to get the same array as above:

SELECT t.id
     , string_to_array(
          concat_ws(
             ','
           , CASE WHEN role1 THEN 'role1' END -- eligible columns
           , CASE WHEN role2 THEN 'role2' END
           , CASE WHEN role3 THEN 'role3' END)
        , ',') AS roles
FROM   tbl t;

We can also generate the list of eligible columns dynamically from the the system catalogs. (If you don't want to spell out columns after all.)

Or narrow down to eligible columns for the json(b) technique to eliminate possibly expensive noise early. See:

• Postgres multiple columns to json

db<>fiddle here - incl. all of the above, a_horse's jsonb query, and a couple of variants

Answer 2

You can convert the row to a JSON value and then use a sub-select:

select t.id, x.roles
from the_table t
  cross join lateral (
     select array_agg(col) as roles
     from jsonb_each_text(to_jsonb(t)) x(col,val)
     where x.val::boolean
       and x.col like 'role%'
  ) x