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?.
drop table if exists table1;
create table table1 (id int, data jsonb);
insert into table1 (id, data) values
(1, '{"type":"pinapple","store1":{"first_added":1989895665,"price":10,"store_id":1,"comments":"some comments..."},"store2":{"first_added":25641165,"price":12,"store_id":321,"comments":"1some comments..."}}');
select * from table1;
DROP TABLE
CREATE TABLE
INSERT 0 1
| id | data |
|---|---|
| 1 | {"type": "pinapple", "store1": {"price": 10, "comments": "some comments...", "store_id": 1, "first_added": 1989895665}, "store2": {"price": 12, "comments": "1some comments...", "store_id": 321, "first_added": 25641165}} |
SELECT 1
update table1
set data = jsonb_set(data, '{store1}', jsonb_build_object('first_added', data->'store1'->'first_added', 'price', 12, 'store_id', 1, 'comments', 'some comments...V2'))
where id = 1;
UPDATE 1
select * from table1;
| id | data |
|---|---|
| 1 | {"type": "pinapple", "store1": {"price": 12, "comments": "some comments...V2", "store_id": 1, "first_added": 1989895665}, "store2": {"price": 12, "comments": "1some comments...", "store_id": 321, "first_added": 25641165}} |
SELECT 1