SQL parse a dictionary of true/false to list of true values

I have a column in the form of a dictionary:

{"black": false, "white": true, "green": true, "blue": false, "red": true, "yellow": false, "pink": false, "orange": true}

I want to turn it into a list of keys that has a true value-

white, green, red, orange

Any idea whaat’s the simplest way to do this?
Preferably without including all the colors in the query itself, as they may be more added in the future.

It’s Postgres SQL if it matters.

Thank you

>Solution :

You can turn the key/value pairs into rows, then aggregate back those where the value is true:

select string_agg(color, ', ')
from json_each_text('{"black": false, "white": true, "green": true, "blue": false, "red": true, "yellow": false, "pink": false, "orange": true}') as j(color,v)
where j.v = 'true';

Leave a Reply