Postgres 12

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 tab ( id INT, jsdata JSONB);
 INSERT INTO tab VALUES 
 (1, '[
    {
        "ModuleId": 1,
        "ModuleName": "XYZ"
    },
    {
        "ModuleId": 2,
        "ModuleName": "ABC"
    }
]')
 

CREATE TABLE



INSERT 0 1


WITH s AS
(
 SELECT ('{'||idx-1||'}')::text[] AS path, jsdata
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata)
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'ModuleId'='1' 
)
UPDATE tab
   SET jsdata = s.jsdata #- path
  FROM s  
  

UPDATE 1


SELECT *
  FROM tab
 ORDER BY id
 


id
jsdata


1
[{"ModuleId": 2, "ModuleName": "ABC"}]



SELECT 1


DELETE FROM tab; 
 INSERT INTO tab VALUES 
 (1, '[
    {
        "ModuleId": 1,
        "ModuleName": "XYZ"
    },
    {
        "ModuleId": 2,
        "ModuleName": "ABC"
    }
]')

DELETE 1



INSERT 0 1


WITH s AS
(
 SELECT ('{'||idx-1||',ModuleId}')::text[] AS path
   FROM tab 
  CROSS JOIN jsonb_array_elements(jsdata) 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'ModuleId'='1' 
)
UPDATE tab
   SET jsdata = jsonb_set(jsdata,s.path,'"CBA"',false)
  FROM s

UPDATE 1


SELECT *
  FROM tab
 ORDER BY id
 


id
jsdata


1
[{"ModuleId": "CBA", "ModuleName": "XYZ"}, {"ModuleId": 2, "ModuleName": "ABC"}]



SELECT 1