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'}
CodePudding user response:
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.
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.