Skip to content
Advertisement

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

Given this table

How can I select it into this result?

Advertisement

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:

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:

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement