how update dynamically attribute in jsonb data column with postgres

Question

Hi i have this structure in my column jsonb in postgres

offer_profile_id (PK, FK): bigint
website_id (PK, FK):    bigint
offer_profile_website_detail: jsonb {
    display_vat_included: boolean
    available_services: [{
        service_id: long
        included_by_default: boolean
        extra_service : boolean
    }]
}

I'm trying to add a new attribute named is_active: boolean inside the available_services. The problem is that the table named "offer_profile", full of data with many rows and which can take a long time to write the insert into "offer_profile" VALUES script.

I'm looking for a dynamic way to update my table.

Regards

Sharif · Accepted Answer · 2021-03-11 06:03:28Z

0

You can use jsonb_set function available in Postgresql. Here is the link. Below is the non-tested version of the code, tweak the path as needed.

UPDATE table_name
SET offer_profile_website_detail = jsonb_set(
offer_profile_website_detail, '{available_services, 0, is_active}', 'true', true)

Test:

create table test
(
    offer_profile_id             bigint,
    website_id                   bigint,
    offer_profile_website_detail jsonb
)

insert into test values (1, 1, '{"display_vat_included": true, "available_services": [{"service_id": 123}]}');
insert into test values (2, 2, '{"display_vat_included": false, "available_services": [{"service_id": 345}]}');
insert into test values (3, 3, '{"display_vat_included": false, "available_services": [{"service_id": 567}]}');

SELECT offer_profile_id, offer_profile_website_detail,
jsonb_set(offer_profile_website_detail, '{available_services, 0, is_active}', 'true', true)
from test

edited Mar 11, 2021 at 6:03

answered Mar 10, 2021 at 11:16

Sharif

3223 silver badges13 bronze badges

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

1 Comment

Hamdy Over a year ago

thank you for your reply, I tested it but not working for me. The error is :path element at position 2 is not an integer. Can you update your comment please ?

Collectives™ on Stack Overflow

how update dynamically attribute in jsonb data column with postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related