Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table jsondata (id int, data jsonb);
INSERT INTO jsondata VALUES 
(1,'[{"cc": null, "subsectionid": 26}, {"cc": null, "subsectionid": 27}]');

CREATE TABLE



INSERT 0 1


with invars as (
  select 27 as subsectionid, 'Lake Providence' as cc
), els as (
  select j.id, ae.rn, 
         case (ae.el->>'subsectionid')::int 
           when i.subsectionid then i.cc
           else ae.el->>'cc' 
         end as cc, 
         ae.el->>'subsectionid' as subsectionid
    from invars i
         cross join jsondata j
         cross join lateral jsonb_array_elements(j.data) with ordinality as ae(el, rn)
   where jsonb_path_exists(j.data, '$[*].subsectionid ? (@ == $x)', 
           jsonb_build_object('x', i.subsectionid))
), newjson as (
  select id, jsonb_agg(els order by rn) - 'id' - 'rn' as newdata
    from els
   group by id
)
update jsondata
   set data = n.newdata
  from newjson n
 where n.id = jsondata.id;

UPDATE 1


select * from jsondata;


id
data


1
[{"cc": null, "id": 1, "rn": 1, "subsectionid": "26"}, {"cc": "Lake Providence", "id": 1, "rn": 2, "subsectionid": "27"}]



SELECT 1