Update partial jsonb field in Postgres 13

can anyone help please optimize SQL request Postgres 13 (jsonb).

Need to update the “percent” values inside the Jsonb field with the specified ID

This example is working, but it works for a very long time on a large database.

select version();

CREATE TABLE collection (
    ID serial NOT NULL PRIMARY KEY,
    info jsonb NOT NULL
);

INSERT INTO collection (info)
VALUES
    (
        '[{"id": "1", "percent": "1"}, {"id": "6", "percent": "2"}]'
    ),
    (
        '[{"id": "5", "percent": "3"}, {"id": "1", "percent": "4"}]'
    ),
    (
        '[{"id": "1", "percent": "5"}, {"id": "2", "percent": "5"}, {"id": "3", "percent": "5"}]'
    );
    
UPDATE collection
                SET info = array_to_json(ARRAY(SELECT jsonb_set(x.original_info,
                                               '{percent}',
                                                 (
                                                     CASE
                                                         WHEN x.original_info ->> 'id' = '1'
                                                             THEN '25'
                                                         ELSE
                                                             concat('"',
                                                                    x.original_info ->> 'percent',
                                                                    '"')
                                                         END
                                                      )::jsonb)
                                        FROM (SELECT jsonb_array_elements(collection.info) original_info) x))::jsonb;

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a521fee551f2cdf8b189ef0c0191b730

Answer

I would phrase this as:

update collection c
set info = (
    select jsonb_agg(
        case when obj ->> 'id' = '1' 
            then x.obj - '{percent}' || '{"percent": "25"}' 
            else x.obj
        end
        order by x.rn
    )
    from  jsonb_array_elements(c.info) with ordinality as x(obj, rn)
)

The logic is quite the same as in your original code, but this skips a few unnecessary casts, and uses direct json array aggregation rather than the intermediate array conversion. Also, I used json operators rather than jsonb_set().

Another important point is that this preserves the ordering of objects in the original json array – while your original code does not.

If there are a lot of rows in the table where no object have the id you search for, then we could pre-filter the data to update only the relevant rows. For this, you can proceed as follows:

update collection c
set info = c1.info
from (
    select c.id, 
        jsonb_agg(
            case when obj ->> 'id' = '1' 
                then x.obj - '{percent}' || '{"percent": "25"}' 
                else x.obj
            end
            order by x.rn
        ) as info
    from collection c 
    cross join lateral jsonb_array_elements(c.info) with ordinality as x(obj, rn)
    group by c.id
    having bool_or(obj ->> 'id' = '1' )
) c1
where c1.id = c.id