Question 90111 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search