Adding boolean field in JSONB

Question

I'm trying to add a new bool field in my JSON object throught jsonb request but it doesn't work. How should I change the request?

update source_settings   
    set settings = settings - 'isEnabled'   ||
                   jsonb_build_object('isEnabled', settings->'isEnabled')   
where settings ? 'isEnabled';

"it doesn't work" isn't a valid Postgres error message nor does it tell us what happens and what you expected to happen. But the expression makes no sense to me. First you remove the key, then you add the key with the existing value. — user330315
– user330315, Commented May 16, 2022 at 7:59
Could you help explaining how to add binary values via jsonb? — RuddaTriduba
– RuddaTriduba, Commented May 16, 2022 at 8:03
Well, your WHERE clause will only update rows that already contain the isEnabled key. If you want to add it when it's not there, you would need where (not settings ? ...). And adding "binary" data is something completely different than "adding a boolean" value — user330315
– user330315, Commented May 16, 2022 at 8:08
settings->'isEnabled' doesn't really make sense as that will at best update it with the current value? — Bergi
– Bergi, Commented May 16, 2022 at 10:16

user330315 · Accepted Answer · 2022-05-16 08:49:45Z

2

If you want to add a new key then just append it using ||

update source_settings   
    set settings = settings || jsonb_build_object('isEnabled', true)   
where not (settings ? 'isEnabled');

The WHERE clause will ensure that only rows are changed where the key does not exist. So rows where settingsalready contain "isEnabled" are not changed.

If the key doesn't exist, then using settings->'isEnabled' doesn't make sense as that will always return null

answered May 16, 2022 at 8:49

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding boolean field in JSONB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related