Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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;

version
PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

3 rows affected

3 rows affected

id	info
1	[{"id": "1", "percent": 25}, {"id": "6", "percent": "2"}]
2	[{"id": "5", "percent": "3"}, {"id": "1", "percent": 25}]
3	[{"id": "1", "percent": 25}, {"id": "2", "percent": "5"}, {"id": "3", "percent": "5"}]