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

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

CodePudding user response：

JSON_EACH_TEXT returns key value pairs. Demo

create table foo (
    id int,
    col json
);

insert into foo values
    (1, '{"black": false, "white": true, "green": true, "blue": false, "red": true, "yellow": false, "pink": false, "orange": true}');

select  foo.id, t.key
from foo
join json_each_text(col) t on value = 'true'

CodePudding user response：

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';