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?.
CREATE TABLE tables(
table_id SERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
user_id VARCHAR(100) NOT NULL,
data jsonb
);
INSERT INTO tables (table_name, user_id, data)
VALUES
('Supplier Info', 4, '[
{"id":"1","first_name":"Fergus","last_name":"Flipsen","email":"fflipsen0@ezinearticles.com","gender":"Male"},
{"id":"2","first_name":"Vincenz","last_name":"Russan","email":"vrussan1@independent.co.uk","gender":"Male"}
]'::JSONb);
1 rows affected
update tables t
set data = (
select
jsonb_agg(
case when (x.obj ->> 'id')::int = 1
then x.obj || '{"first_name": "Marcus"}'
else x.obj
end
order by x.ord
) new_data
from jsonb_array_elements(t.data) with ordinality x(obj, ord)
)
1 rows affected
select * from tables;
| table_id | table_name | user_id | data |
|---|---|---|---|
| 1 | Supplier Info | 4 | [{"id": "1", "email": "fflipsen0@ezinearticles.com", "gender": "Male", "last_name": "Flipsen", "first_name": "Marcus"}, {"id": "2", "email": "vrussan1@independent.co.uk", "gender": "Male", "last_name": "Russan", "first_name": "Vincenz"}] |