Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (id int, role1 bool, role2 bool, role3 bool, irrelevant text);

INSERT INTO tbl VALUES
  (1, true,false,true, 'endless blabla')
, (2, true,true,true, 'endless blabla')
, (3, false,false,false, 'endless blabla')
, (4, true,false,NULL, 'endless blabla')
, (5, NULL,NULL,NULL, 'endless blabla');
5 rows affected

-- VALUES expression to pivot columns
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);
   


id
roles


1
{role1,role3}

2
{role1,role2,role3}

3
{}

4
{role1}

5
{}


-- simple solution with CASE returning string
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;


id
roles_string


1
role1,role3

2
role1,role2,role3

3


4
role1

5



-- simple solution with CASE returning array
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;


id
roles


1
{role1,role3}

2
{role1,role2,role3}

3
{}

4
{role1}

5
{}


-- a_horse's query with jsonb
select t.id, x.roles
from   tbl 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;


id
roles


1
{role1,role3}

2
{role1,role2,role3}

3
null

4
{role1}

5
null


-- variant with only relevant columns and ARRAY constructor
SELECT t.id, a.roles
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT col
   FROM   jsonb_each(row_to_json((SELECT d FROM (SELECT role1, role2, role3) d))::jsonb) x(col,val)
   WHERE  x.val = 'true'
   )) a(roles);



id
roles


1
{role1,role3}

2
{role1,role2,role3}

3
{}

4
{role1}

5
{}


-- variant with json and json_build_object()
SELECT t.id, a.roles
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT ARRAY(SELECT col
   FROM   json_each_text(json_build_object('role1', role1, 'role2', role2, 'role3', role3)) x(col,val)
   WHERE  x.val::boolean
   )) a(roles);


id
roles


1
{role1,role3}

2
{role1,role2,role3}

3
{}

4
{role1}

5
{}