Postgres 15

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}]');

SELECT * from jsondata;

CREATE TABLE



INSERT 0 1




id
data


1
[{"cc": null, "subsectionid": 26}, {"cc": null, "subsectionid": 27}]



SELECT 1


UPDATE jsondata
SET data = data || '{null: ["Lake Providence"]}'::jsonb
WHERE data->>'subsectionid' = '27'
returning *;

ERROR:  invalid input syntax for type json
LINE 2: SET data = data || '{null: ["Lake Providence"]}'::jsonb
                           ^
DETAIL:  Expected string or "}", but found "null".
CONTEXT:  JSON data, line 1: {null...


UPDATE jsondata
SET data = jsonb_path_query_array(data,'$[*] ?(@.subsectionid<>27)') 
           || '{"subsectionid":27,"cc": ["Lake Providence"]}'::jsonb
WHERE data @? '$[*].subsectionid ?(@==27)'
returning jsonb_pretty(data);


jsonb_pretty


[
    {
        "cc": null,
        "subsectionid": 26
    },
    {
        "cc": [
            "Lake Providence"
        ],
        "subsectionid": 27
    }
]



UPDATE 1